Two new courses on Advanced Corruption Recovery Techniques and Maintenance Plans

I’ve just heard from our good friends at Pluralsight that our two latest online training courses are now live and available for watching! My latest course is SQL Server: Advanced Corruption Recovery Techniques which follows on from my earlier SQL Server: Detecting and Recovering from Database Corruption course. The course is 4 hours long and has […]

New online course: Detecting and Correcting Database Corruption

My latest Pluralsight online training course went live today: SQL Server: Detecting and Correcting Database Corruption It’s 4 hours long and is the first in a two-part series, with a more advanced course coming in a few weeks. The modules in this course are: Introduction Causes of Database Corruption Detecting Page Corruption Consistency Checking DBCC […]

The Accidental DBA (Day 11 of 30): Backups: Backup Storage and Retention

This month the SQLskills team is presenting a series of blog posts aimed at helping Accidental/Junior DBAs ‘keep the SQL Server lights on’. It’s a little taster to let you know what we cover in our Immersion Event for The Accidental/Junior DBA, which we present several times each year. If you know someone who would benefit from […]

The Accidental DBA (Day 10 of 30): Backups: Backup Testing for Validation

This month the SQLskills team is presenting a series of blog posts aimed at helping Accidental/Junior DBAs ‘keep the SQL Server lights on’. It’s a little taster to let you know what we cover in our Immersion Event for The Accidental/Junior DBA, which we present several times each year. If you know someone who would benefit from […]

The Accidental DBA (Day 9 of 30): Backups: Essential BACKUP Options

This month the SQLskills team is presenting a series of blog posts aimed at helping Accidental/Junior DBAs ‘keep the SQL Server lights on’. It’s a little taster to let you know what we cover in our Immersion Event for The Accidental/Junior DBA, which we present several times each year. If you know someone who would benefit from […]

The Accidental DBA (Day 8 of 30): Backups: Planning a Recovery Strategy

This month the SQLskills team is presenting a series of blog posts aimed at helping Accidental/Junior DBAs ‘keep the SQL Server lights on’. It’s a little taster to let you know what we cover in our Immersion Event for The Accidental/Junior DBA, which we present several times each year. If you know someone who would benefit from […]

The Accidental DBA (Day 7 of 30): Backups: Recovery Models and Backup Types

This month the SQLskills team is presenting a series of blog posts aimed at helping Accidental/Junior DBAs ‘keep the SQL Server lights on’. It’s a little taster to let you know what we cover in our Immersion Event for The Accidental/Junior DBA, which we present several times each year. If you know someone who would benefit from […]

The Accidental DBA (Day 6 of 30): Backups: Understanding RTO and RPO

This month the SQLskills team is presenting a series of blog posts aimed at helping Accidental/Junior DBAs ‘keep the SQL Server lights on’. It’s a little taster to let you know what we cover in our Immersion Event for The Accidental/Junior DBA, which we present several times each year. If you know someone who would benefit from […]

Easy automation of SQL Server database maintenance

A while back I kicked off a survey asking what mechanism you use for running your regular SQL Server database maintenance. Here are the results: The “Other” responses were: 7 x “A combination of maintenance plans generated from SSMS wizard and a home-grown index maintenance script” 5 x “Combo of own and modified scripts of […]

Survey: what mechanism do you use for regular database maintenance?

I haven’t done any surveys this year and I’d like to get back into doing one every week or so for the next few months. In this survey I’d like to know what mechanism(s) you use to perform your regular database maintenance. I’ll editorialize the results in a week or so. Thanks!

DBCC CHECKDB scalability and performance benchmarking on SSDs

Back in February I ran a bunch of performance tests of DBCC CHECKDB on SSDs, to evaluate the effects of degree-of-parallelism (DOP) and various options and traceflags, and now I’m finally getting around to presenting the results. Make sure to also read the recent post where I talk about the detrimental effect of computed-column indexes on […]

DBCC CHECKDB performance and computed-column indexes

It’s no secret that DBCC CHECKDB has some performance quirks based on the schema of the database being checked and various kinds of corruptions. I was recently doing some scalability testing of DBCC CHECKDB for a blog post and discovered quite a nasty performance issue that exists in all versions of SQL Server back to […]

Buffer pool disfavoring

Have you ever wondered why DBCC CHECKDB, for instance, doesn’t flush the buffer pool when it runs on a database that is larger than the memory capacity of the server? There’s cool mechanism that it uses called disfavoring. When a buffer is no longer required, the buffer can be marked as the least recently used of […]

Multiple log files and why they’re bad

About a month ago I kicked off a survey with some code to run to figure out how many log files your databases have (see here). There are all kinds of misconceptions about transaction logs and how to configure them (and how they work) and I'm amazed at the misinformation I continue to see published. For […]

New script: When were the sp_configure options last changed?

In my previous post I explained how the sp_configure settings are stored in a special page in the master database called the CONFIG block. Sometimes you might want to know when these were last changed if error logs are not available for some reason (as sp_configure changes are noted in the error log) or someone […]

Where are sp_configure settings stored? Another reason to backup master…

Earlier today I blogged on our SQL Server Pro magazine blog about false-alarm corruptions you will *definitely* see if you restore a backup of the master database as a user database and run DBCC CHECKDB on the restored copy. You might be doing this as part of offloading consistency checks to another server or validating […]

Importance of how you run consistency checks

Back in 2009 I ran a survey about methods of running consistency checks. I recently re-ran the survey to get a better idea of the prevailing methodologies – hoping that more people were offloading consistency checks in concert with testing backups. The results are very interesing. 2009 survey results: 2012 survey results: The results are […]

Survey: how do you run consistency checks?

Three years ago I ran a survey about consistency checking methods. A lot has changed since then, including database sizes, 24×7 operations, and a lot more people reading my blog. I'd like to re-run that survey so we can all get a better idea of what the prevailing methodologies are these days. So, if you […]

Who is watching over your precious SQL Servers while you’re busy?

I bet you'd love the answer to be "SQLskills.com"? Well now it can be. Over the last few months some of our clients for whom we've performed SQL Server health checks on their critical servers have asked us if we'd consider a regular service where we perform mini-health checks on those same servers and also […]

SharePoint in a SQL Server environment

A few weeks back I kicked off a survey asking whether you have SharePoint in your SQL Server environment at all. Here are the results:   The "Other" responses were: 3 x "Not now but I did in a previous role." 3 x "Yes, but the dbas do not manage their servers." 2 x "SharePoint 2007 […]

Survey: do you have SharePoint in your SQL environment?

This question came up on the #sqlhelp tag on Twitter today, and it's something I'm interested in too: what percentage of SQL Server environments include SharePoint somewhere too? This is interesting because it has implications for standardization of SQL Server maintenance and HA/DR procedures across your company. If you don't have SharePoint, please take 5 […]

SharePoint 2010 database maintenance whitepaper

As you may know, I've been teaching the SQL Server portion of the SharePoint MCM since it started. The old database maintenance whitepaper for SharePoint 2007 had all kinds of things wrong with it and the publishing of the updated whitepaper for SharePoint 2010 was eagerly awaited by the community. Unfortunately it too had a bunch […]

Performance issues from wasted buffer pool memory

Back in April I kicked off a survey where I asked you all to send me some information about your buffer pools – how much memory is being used for data file pages and how much of that memory is storing empty space. I got back data from 1394 servers around the world – thanks! […]

Survey: how much server memory is being wasted? (code to run)

In this survey I'd like you to run some code and then send me the results (and I'm sure someone will put together a PowerShell script to make it easy to run on multiple instances). I want to know how much of your precious server memory is being wasted storing empty space on data file […]

Tempdb configuration survey results and advice

A few weeks ago I kicked off a survey about tempdb configuration – see here for the survey. I received results for more than 600 systems! Here they are:     These are very interesting results, for several reasons: It shows the relative distribution of core-count for SQL Servers, with a pronounced shift to 8+ […]

Updated database size survey results

Last week I kicked off a survey about the size of the largest single SQL Server database in your company – see here for the survey. I was interested to see how the distribution has changed since I did the same survey a few years ago. 2011 results:   2009 results:   Very interesting! As […]

Survey: what’s the largest SQL Server database at your company?

In my survey this week I'd like to find out how large the largest single SQL Server database is at your company (how much space does the data take up on disk, roughly). I ran a similar survey almost two years ago and I think it will be interesting to compare the results. [Edit: the […]

Importance of network latency when using database mirroring

Last week I kicked off a survey about network latencies and database mirroring. See here for the original post. Here are the results of the survey:   I was really interested to see whether the proportion of people doing asynchronous mirroring became higher as the network latency increased. Although this isn't a statistically valid sampe […]

Holiday special offer on maintenance+operations audits

Happy Holidays! Back in January I ran a special offer on our new (at the time) auditing service, and as the holidays approach I'd like to do it again! What I'm offering: I'll perform a remote maintenance/operations audit for US$750 for the first 10 customers to sign up by January 15th, limited to 4 hours […]

TechNet Magazine Article: Top-10 Secrets of a SQL Server Expert

The November 2010 edition of TechNet Magazine is now available on the web and contains my latest feature article. In this article I give my top-10 secrets for success as a DBA, covering: Take inventory Standardize configurations Understand the I/O susbsystem Create a customized maintenance plan Ensure the security of your system Get on good terms with […]

TechNet Magazine: May 2010 SQL Q&A column

The May edition of TechNet Magazine is available on the web now and has the latest installment of my regular SQL Q&A column. This month's topics are: How to change the file locations for a user database The difference between page latches and page I/O latches Why database snapshots are not a good substitute for […]

A SQL Server DBA myth a day: (30/30) backup myths

(Look in the Misconceptions blog category for the rest of the month’s posts and check out the 60-page PDF with all the myths and misconceptions blog posts collected together when you join our Insider list, plus my online Myths and Misconceptions training course on Pluralsight.) The month is finally over so time for the grand […]

Paul and Kimberly interview from MVP Summit

For about 6 months we'd bee trying to hook up online with SharePoint MVP Hilton Geisenow for an interview but unfortunately the bandwidth to South Africa is pretty limited so we decided to wait until the recent MVP Summit in Redmond, WA. We managed to hook up and record an interview for Hilton and he's just published […]

A DBA myth a day: (13/30) you cannot run DMVs when in the 80 compat mode (T-SQL Tuesday #005)

(Look in the Misconceptions blog category for the rest of the month’s posts and check out the 60-page PDF with all the myths and misconceptions blog posts collected together when you join our Insider list, plus my online Myths and Misconceptions training course on Pluralsight.) This blog post is part of two series – my […]

Important things for DBAs to consider

(Look in the Misconceptions blog category for the rest of the month’s posts and check out the 60-page PDF with all the myths and misconceptions blog posts collected together when you join our Insider list, plus my online Myths and Misconceptions training course on Pluralsight.) Over the last month or so I've found myself handing […]

Inside sys.dm_db_index_physical_stats

Way back in the mists of time, at the end of the last century, I wrote DBCC SHOWCONTIG for SQL Server 2000, to complement my new invention DBCC INDEXDEFRAG. I also used to wear shorts all the time, with luminous orange, yellow, or green socks. Many things change – I now have (some) dress sense, […]

In the news: our maintenance audits get rave reviews

Back in January I offered a promotion as a way of introducing our maintenance/operations auditing services. Now I've completed a bunch of them, with some excellent results for customers. One international customer, Plex Systems, was so pleased with the outcome of my audit of their manufacturing ERP software hosting databases that they issued a press release today […]

New script: is that database REALLY in the FULL recovery mode?

One of the perennial problems facing both experienced and involuntary DBAs is how to tell whether a database is really in the Full recovery model or not? This is complicated by the fact that when you switch a database into the Full recovery mode, it actually behaves as if it’s in the Simple recovery mode […]

DBCC CHECKFILEGROUP bug on SQL Server 2008

Here's an interesting bug that surfaced recently, first reported by Bryan Smith on the MSDN disaster recovery/HA forum three weeks ago. One of the mechanisms I advise for running consistency checks on VLDBs with multiple filegroups is to run successive DBCC CHECKFILEGROUP operations on the filegroups holding the partitions (see CHECKDB From Every Angle: Consistency Checking Options […]

Survey: how do you create and maintain statistics

This survey is based on a suggestion from Jonathan Kehayias (blog/Twitter). In this survey I'm asking two questions: When you're designing/implementing a database, do you manually create statistics or let SQL Server create them automatically? How do you maintain statistics? Manually or letting SQL Server do it for you? Or both?  I'll probably collaborate with Kimberly on […]

CHECKDB From Every Angle: How to tell if data purity checks will be run?

Here’s a question that came up recently: if I’ve upgraded a database from SQL 2000 or before, how can I tell if the data purity checks will be run or not? As you may know, DBCC CHECKDB in 2005 onwards includes ‘data purity’ checks. These look for column values where the value is outside the […]

Importance of running regular consistency checks

A long time ago, in a blog post far, far away (well before I went offline in July) I kicked off a weekly survey about how often you run consistency checks (see here for the survey). Now I'm back online again, and so here are the results as of 8/3/09.   The results are actually […]

Weekly survey: how often do you run consistency checks?

In this week's survey I'd like to know how often you run consistency checks on your *most critical* production database, regardless of *how* you run them (we did that survery already – see Importance of how you run consistency checks). I'll report on the results around July 4th. I'd only like you to answer for […]

Importance of data file size management

In last week's survey I asked how you manage the size of your database *data* files – see here for the survey. Here are the results as of 6/24/09. The 'other' values are as follows: 5 x 'manual file growths and a custom mom alter to when the datafiles are 98% full. autogrow set to […]

Why you should not shrink your data files

(Be sure to join our community to get our bi-weekly newsletter with exclusive content, demo videos, and other SQL Server goodies! Also check out our online training courses.) One of my biggest hot-buttons is around shrinking data files. Although I used to own the shrink code while I was at Microsoft, I never had a […]

Weekly survey: how do you manage your data file sizes?

In this week's survey I'd like to know how you manage the sizes of your database *data* files (remember we've already done log file size management). I'll report on the results around 6/21/09. I say this every week in the PS, but I'm moving it up here because I don't like having to delete comments […]

Issues around DBCC CHECKDB and the use of hidden database snapshots

There are a couple of issues that I’ve heard of in the last few weeks (one while onsite at a customer) and I think they might bite some people so I’d like to share them with you. DBCC CHECKDB in 2005 onwards uses a hidden database snapshot to create the transactionally-consistent point-in-time view of the database that […]

Backup monitoring and reporting

Chad Miller over on SQLServerCentral has put together a really great collection of scripts that allow you to easily monitor and report on what backups are happening on your system. These should be especially useful for involuntary DBAs, rather than having to dig into the backup history tables themselves. I'll be including a link to […]

Importance of defining and measuring SLAs

In last week's survey I asked you two things, as a precursor to a whitepaper I'm writing for Microsoft The first question was what is your maximum allowable downtime SLA (either for 24×7 operation or not). See here for the survey. Here are the results as of 5/30/09.   The Other values were all about not having […]

Weekly survey: maximum allowable downtime – target and actual

This week's survey is inspired by this morning's Kimberly+Paul hot-tub conversation around data-dependent routing vs. network load balancing, which then turned to SLAs. Yes, we lead an exciting life . This survey is a *four*-parter. Part 1 is for each of your databases, which survey option is closest to your target maximum allowable downtime SLA (Service-Level […]

New script: how much data will the next log backup include?

About a year ago, I blogged a cool script that would work out how much of a database has changed since the last full backup – i.e. how big will the next differential backup be. You can find that script at New script: How much of the database has changed since the last full backup?. […]

Weekly survey: have you ever tested your disaster recovery plan?

This week's survey is inspired from many stories I saw on the forums and Twitter this week – mostly bad, one good (someone I'm following is spending the weekend testing their disaster recovery plan – cool!). I'd like to know whether you're ever tested your disaster recovery plan, and if so, what happened? I'll report […]

Clustered or nonclustered index on a random GUID?

Quickie this morning to start the day off. I saw a question on a forum: if I *have* to use a GUID and *must* have a primary key, should I make the primary key clustered or nonclustered? Now, I'm not getting into the whole GUID vs. bigint identifier, or random GUID vs. GUID generated by […]

A sad tale of mis-steps and corruption (from today)

This is a true story, and unfolded over the last few days. It's deliberately written this way, I'm not trying to be patronizing – just illustrating the mistakes people can make if they don't know what not to do. Once upon a time (well, a few days ago), there was a security person who had access […]

Importance of having a manageable environment

Last week's survey was two-fold – what's the largest SQL Server database you manage, and how many SQL Server databases are you responsible for managing. Here are the results as of 5/10/2009.   As far as the database sizes are concerned, the distribution curve isn't surprising, but the average size of databases *is*. 70% of […]

Misconceptions around corruptions: can they disappear?

This has come up a few times now, most recently in an email question this morning - subsequent runs of DBCC CHECKDB show varying numbers of corruptions, and sometimes no corruptions – what's going on? Even more strange – a maintenance job runs a DBCC CHECKDB, which shows errors, but then in the morning – no […]

Do transactions rollback when DBCC CHECKDB runs?

Recently there’s been a spate of people noticing strange behavior from active transactions when DBCC CHECKDB (or any of the other DBCC consistency checking commands run). For example, I’ve create a database call DbccTest with a single table. In one connection I do: BEGIN TRAN INSERT INTO t1 VALUES (1, 1); GO And in another […]

Weekly survey: what’s your largest database, and how many do you manage?

Jason Massie posted an interesting statistic yesterday – Facebook has 1.5 petabytes of image storage, and it grows by 25TB daily – I wonder how they store and manage it? In this week's survey, I'm interested in two things: what's the largest single SQL Server database in your company and how many SQL Server databases […]

Importance of having the right backups

Last week's survey was on what kind of backups you take, along with the recovery model used (see here for the survey). Here are the results as of 5/2/2009.   The 'other' responses were combinations of the other answers. This survey is a bit of a pre-cursor to my article on Understanding SQL Server Backups […]

TechNet Magazine: June 2009 SQL Q&A column

The June edition of TechNet Magazine is available on the web now and has the latest installment of my regular SQL Q&A column. This month's topics are: Combining index and statistics maintenance How some Enterprise-only features can prevent a database restoring on lower editions Why database mirroring can switch between SYNCHRONIZED and SYNCHRONIZING Use a […]

Weekly survey: what kind of backups do you take?

In this week's survey I'm interested in what kind of backups you take, and also what recovery model you mostly use. If you have multiple database with different strategies, by all means respond multiple times. The more responses the better! I'll report on the survey results some time over the weekend of 5/2/2009. A couple […]

Physical database layout vs. database size

A couple of weeks ago I kicked off the latest survey, on what the physical layout of your databases are and why you have them that way (see here for the survey). I let the survey run for a while to get a good sampling, and I wasn't disappointed, with over 1000 responses! Here are […]

Misconceptions around TF 1118

There’s been a recent flurry of confusion and misconceptions about trace flag 1118 in SQL Server 2008. This trace flag switches allocations in tempdb from single-page at a time for the first 8 pages, to immediately allocate an extent (8 pages). It’s used to help alleviate allocation bitmap contention in tempdb under a heavy load […]

Why could restoring a log-shipping log backup be slow?

A short post to start the day (in India) and then at lunchtime I'll do how it works: FILESTREAM garbage collection. This is a question that came up recently on an MVP discussion list: why does a 200MB log backup take 40 minutes to restore on my log-shipping secondary? First answer I thought of was […]

Inside the Storage Engine: More on the circular nature of the log

  Every so often (well, ok, very frequently) my internal list of ‘I must blog about that’ gets too big and I have an almost uncontrollable urge. Luckily, these urges end up with me blogging several times, otherwise who knows what kind of trouble I’d get into? :-) First up today is some pretty deep internals […]

SQL 2005 SP2 maintenance plan bug masking corruption

Not much to do here in Tokyo while waiting for the Bangkok flight except read and blog, and I've already done enough reading for one day on the flight here – so that leaves blogging! Here's an interesting case that I got involved in on SQLServerCentral (here's the original thread, linked here with permission of […]

Weekly survey: does size really matter – or is it what you do with it?

This week's survey is a little more complicated. I'm interested in the physical layout of your databases. I've got four surveys, for a variety of database sizes. Please vote multiple times in each survey, as you see fit – and by all means forward this link to your friends/clients/etc or re-blog/tweet it. I'm going to report […]

Importance of proper transaction log size management

Last week's survey was on how *you* manage the size of your transaction log (see here for the survey). Here are the results as of 4/10/2009 – the most popular survey yet: In my opinion (guarantee that I'm going to say stuff that some of you won't agree with!), there are only two valid answers in […]

Weekly survey: transaction log size management

In this week's survey, I'm interested in how *you* manage the size of your transaction log. I'll report on the results around 4/10/2009. Thanks! (No comments please… wait till the results post next week) 

Importance of choosing the right LOB storage technique

Last week's survey was on how you should store large-value character data in SQL 2005+ (see here for the survey). Here are the result as of 4/3/2009 – and I think my favorite answer is starting to catch-on: My favorite answer is, of course, it depends! For all those who didn't answer 'it depends', your […]

Index rebuilds depend on stats, which are updated by index rebuilds?!?

Jack Li, one of the Senior Escalation Engineers in Product Support, just posted details of an interesting case over on the CSS blog - his article is here. It talks about index builds and rebuilds, but the issue is the same for both, so I'll just talk about rebuilds. The jist of the problem is that […]

How to change a default constraint

While I was teaching the MCM-Database class last week, we were discussing fragmentation and the effect of a high-order GUID key on an index. Without going into too many details, having a random GUID – as generated from the NEWID() - function is bad, but having one generated by NEWSEQUENTIALID() isn't anyway near so bad (I'll […]

Weekly survey: method of running consistency checks

In this week's survey, I'd like to know *how* you run consistency checks, not how often. I'll report on the results next week (around 3/27/09) Thanks!

Importance of index maintenance

Last week’s survey was on what kind of regular index maintenance you perform (see here for the survey) as a way of kicking off a new series I’m writing around index maintenance. Here are the results as of 3/21/09 – I find them very encouraging: As you can see, about 2/5 of respondents are performing some form […]

Weekly survey: index maintenance plan

I'm about to start a new series of post about index fragmentation and removing it. For this week's survey, I'd like to know what index maintenance you do to address fragmentation (in any of its forms) – I'll report on it in a week. Thanks

Importance of validating backups

Last week I kicked off the first weekly survey – on whether you validate your backups or not (see here for the survey). The results are very interesting (as of 3/13/09): As you can see, almost 25% of respondents never validate their backups! And a further 25% only validate them occasionally, with 30% doing some […]

Great script for automating database maintenance

Ola Hallengren, who we meet every so often at SQL Connections, has a great script that helps automate consistency checking, backups, and index maintenance. He's constantly updating it and it's good quality code. Check out the latest version for  SQL Server 2005 and 2008: Release notes Documentation Script Enjoy! 

TechNet Magazine: April 2009 SQL Q&A column

The April edition of TechNet Magazine is available on the web now and has the latest installment of my regular SQL Q&A column. This month's topics are: Disappearing errors with DBCC CHECKDB Provisioning tempdb when moving from 2000 to 2008 Does fillfactor prevent fragmentation and should it be set instance-wide Avoiding FILESTREAM performance problems Check […]

CHECKDB From Every Angle: SQL and Windows bugs around database snapshots

As you may know, DBCC CHECKDB (and some of the other DBCC CHECK*) commands use an internal database snapshot to get a transactionally consistent view of the database (if you didn't, see my blog post CHECKDB From Every Angle: Complete description of all CHECKDB stages for an explanation). It's entirely possible that the database snapshot may […]

How can a log backup fail but a full backup succeed?

This is an interesting case that cropped up yesterday – the transaction log is damaged so a log backup doesn't work (with the error below): Backup detected log corruption in database FakeDBName. Context is FirstSector. LogFile: 2 'F:\SQLLOGS\XYZ\FakeDBName_Log.ldf' VLF SeqNo: x502e VLFBase: x2ce40000 LogBlockOffset: x2d0a9000 SectorStatus: 2 LogBlock.StartLsn.SeqNo: x4ee3 LogBlock.StartLsn.Blk: x1348 Size: x200 PrevSize: x400 […]

Testing a new survey method: backup validation

I'm toying with the idea of having a weekly survey that'll highlight an interesting facet of database management. I've signed up with SurveyPopups.com, which is free and allows you to see the results as you vote on them. If you think this is a cool idea, vote in the survey using the options below. If […]

Database settings survey

We're doing some research into database settings that people use and the best way to get a good variety of systems is to ask you to send us some data. We're interested in whether people are using the defaults or changing them in any way. If your're interested, run this query (in the context of […]

Easy monitoring of high-severity errors: create Agent alerts

In my previous post today I talked about error 825 and how you should have an Agent alert set up to catch it when it happens. There are two ways you can set up Agent alerts – using SSMS or using the stored-proc sp_add_alert. In a post from April 2008 I showed how to create an […]

A little-known sign of impending doom: error 825

There are two pretty well-known I/O errors – 823, and 824 – but there's also one called 825 which most DBAs do*not* know about, and definitely should. From SQL Server 2005 onwards, if you ever see an 823 or 824, SQL Server has actually tried that I/O a total of 4 times before it finally […]

Corruption errors: Msg 5242, Level 22

In SQL Server 2000 and before, the symptoms of database corruption would occasionally manifest themselves as asserts, such as: SQL Server Assertion: File: <recbase.cpp>, line=1378 Failed Assertion = 'm_offBeginVar < m_sizeRec'. To reduce the number of assertions being fired by the SQL Engine, my team changed these asserts into real error messages for SQL Server […]

Performance impact of lots of VLFs in the transaction log

One of the things I mentioned in my recent TechNet Magazine article on Understanding Logging and Recovery was the need to manage the transaction log so the number of VLFs (virtual log files) does not get too large (with too large being more than, say, 100 VLFs). Linchi Shea (a fellow MVP) has just posted […]

TechNet Magazine: feature article on understanding logging and recovery

Wow – today is all about new content. As if I haven't already blogged about enough stuff to keep you reading through next week, the February issue of TechNet Magazine is now available and contains a feature article I wrote about understanding how logging and recovery work inside SQL Server. The article covers: What is […]

Comprehensive tempdb blog post series

Over the last few weeks Sunil Agarwal (from the SQL Storage Engine team) has posted a great series of blog articles about tempdb and the version store, over on my old stomping ground – the Storage Engine blog. The articles are well worth reading – the links are: Managing tempdb part 1 and part 2 Version […]

TechNet Radio interview on database corruption

Well, we're back from vacation finally (only for 10 days and then off to India and Thailand for 3 weeks… phew) and I've got a bunch of blogs posts to catch up on. First up – I did an interview with TechNet Radio in mid-December where I talked about database corruption and things to do […]

TechEd demo: nonclustered index corruption

This blog post describes the demo "2 – NC Indexes" from my Corruption Survival Techniques conference session from various conferences in 2008. The links to the scripts and databases to use are in this blog post. Edit 6/4/2012: Be aware that in versions of SQL Server from 2008 onwards, you may only be able to […]

TechEd: 80 minute video of Corruption Survival Techniques presentation

For those of you who couldn't make it to a conference this year where I presented my Corruption Survival Techniques session, the folks at TechEd EMEA have just posted an 80 minute long video of the presentation I did in Barcelona in early November. It walks through I/O errors, what CHECKDB does, how it works, […]

Conference Questions Pot-Pourri #10: Shrinking the database before taking a backup

Here's a question I got from someone who attended our database maintenance workshop at PASS last week (paraphrased): I attended your pre-conference session on database maintenance and found it to be very informative.  From what you told use though, I think I need to change my nightly backup procedure.  I like to get my databases […]

Conference corruption demo scripts and example corrupt databases

This blog post explains the demo scripts and databases I've posted to cover all the Corruption Survival Techniques and DBCC CHECKDB sessions I've presented at conferences this year. There are two zip files you need to download: the example corrupt databases (36-MB zip) (and this one if you're on 2008) and the demo scripts. These […]

TechNet Magazine: October SQL Q&A column

It’s been almost two weeks since my last post as we’ve been offline in Scotland (another photo post to follow – I owe you two now…) but now we’re back for the crazy Fall conference and teaching season. The latest installment of my regular Q&A column in TechNet Magazine is available at http://technet.microsoft.com/en-us/magazine/cc895648.aspx. This month […]

Follow-on from instant initialization privilege checking

I got bored on the first leg of the journey from Seattle to London so thought I’d bang out a quick blog post. After my previous post on checking whether a SQL instance is able to use instant initialization (see here), I had a discussion with Scott R., who regularly comments on blog articles. He […]

RunAs Radio interview on being an “involuntary DBA”

On Tuesday I recorded a couple of sessions with Richard and Greg on RunAs Radio. It’s been a while since either Kimberly or I have been on the show – last November at TechEd in Barcelona. In the first session we discussed what it is to be an “involuntary DBA” – someone who’s thrown into the […]

Should Microsoft provide a transaction log reader tool?

Now this one’s sure to spark some controversy… I was checking my RSS feeds of the SQL blogs that I follow and noticed an interesting post on Kalen’s blog (the post is here). She’d been lambasted for suggesting in the weekly SQL Server Magazine newsletter that *Microsoft* shouldn’t provide a log reader tool and asked […]

Search Engine Q&A #25: Why isn’t my log backup the same size as my log?

I woke up this morning and someone had replaced my wife with someone who likes to blog :-). Kimberly’s turned over a new leaf and is going to blog much more often – in fact she’s blogged 4 times today already. Check out her blog here. Continuing on the transaction log theme of the last few […]

SQL Server 2008: Does my database contain Enterprise-only features?

Moving databases around is pretty common, as is moving databases between servers running different Editions of SQL Server, especially during a disaster recovery situation. You may not know this, but in SQL Server 2005, if you had partitioning anywhere in a database, you could only attach/restore that database using an Enterprise or Developer instance. I […]

Search Engine Q&A #24: Why can’t the transaction log use instant initialization?

This is a quickie in response to a blog comment from my previous post on instant initialization: How to tell if you have instant initialization enabled? The comment was: I must say, I love instant initialization. It's awesome. But I always wondered why it's not available for the log file. I assume there's a technical reason… […]

Can GUID cluster keys cause non-clustered index fragmentation?

At the user group meeting on Monday I spent some time explaining how GUIDs can cause fragmentation in clustered indexes AND in non-clustered indexes, even if the GUID isn’t specifically included in the non-clustered index key. GUIDs are essentially random values (pseudo-random in ranges if generated using NEWSEQUENTIALID) that are also unique. Their uniqueness is […]

Search Engine Q&A #23: My transaction log is full – now what?

There's been a spate of problems on the forums and in my inbox with people's transaction logs filling up. With the increase in "involuntary DBAs", there are more people being thrust into the role of database administration without being given the chance to learn the skills, tips, and tricks necessary. One of the most common […]

Search Engine Q&A #22: Can all page types be single-page restored?

I've had a few follow-ups on my two posts about boot page and file header page corruption – asking if its possible to do single-page restore operations for these pages. Let's try: CREATE DATABASE BootPageTest; GO – Single page restore is only possible using the FULL recovery model ALTER DATABASE BootPageTest SET RECOVERY FULL; GO […]

How to tell if you have instant initialization enabled?

As you may already know, instant initialization is a way to prevent data (not log) file create and grow operations having to zero-initialize the new space before allowing it to be used. This can vastly speed up these operations as zero-initialization can take a *long* time for large files. This is especially useful in disaster […]

TechNet Magazine: Effective Database Maintenance article and August SQL Q&A column

Well, we’re just back from vacation (photo blog post to follow) and I’ve heard that the feature article on Effective Database Maintenance I wrote for the August issue of TechNet Magazine is live on the web. It also includes a 5 minute long screencast I recorded where I demo the effect of database shrink on index […]

Search Engine Q&A #19: Misconceptions around index rebuilds (allocation, BULK_LOGGED mode, locking)

Over the last few weeks I've seen (and helped correct) quite a few myths and misconceptions about index rebuild operations. There's enough now to make it worthwhile doing a blog post (and it's too hot here in Orlando for us to go sit by the pool so we're both sitting here blogging)… Myth 1:  index […]

In Print: TechNet Magazine June 2008 SQL Q&A Column

My first magazine article is in print! I’ve taken over the bi-monthly SQL Q&A column for TechNet Magazine and I just received the June magazine in the mail today with my first column in it. Topics covered are: Creating corruption and using page checksums The shrink-grow-shrink-grow trap How many databases can be mirrored per instance […]

Conference Questions Pot-Pourri #7: How to create Agent alerts

Many times I've been asked to do a blog post about creating Agent alerts, and given that today I demo'd it as part of our Accidental DBA workshop at Connections, it seemed a good time to do the blog post too! I demo this in the context of alerting a DBA when an 823 or […]

New script: How much of the database has changed since the last full backup?

Over the weekend there was a question on one of the internal aliases at MS: how can I tell what percentage of a database has changed since the last full backup, so I can choose between a differential or full backup? No such code exists as far as I know – until now! I happened […]

CHECKDB From Every Angle: When did DBCC CHECKDB last run successfully?

This came up several times during the week so I thought it was about time to blog about it. One of the new features we put into SQL Server 2005 was storing the last time that DBCC CHECKDB completed successfully (called the last-known good time). What does successfully mean? This is the catch – if […]

Search Engine Q&A #17: Split a VLDB into filegroups or smaller databases for backups?

Here’s an interesting question that came in to our questions line (questions@SQLskills.com - no guarantee of an answer – I check it every so often): I have seen demonstrations where a large database being broken down into smaller ones using synonym names. I think it was used on a data warehouse and allowed smaller database backups […]

Available for download: Our High-Availability hands-on labs and SQL Server 2008 JumpStart materials

A couple of weeks ago I blogged about the three tracks of the SQL Server 2008 JumpStart course that SQLskills.com taught internally for Microsoft and some MVPs – see here for details. Well, the content is now available to download! Note that this was based on CTP-5 (November 2007 CTP) and there have been *lots* […]

CHECKDB From Every Angle: Example DBCC CHECKDB run-times

Almost a year ago to the day I asked a question on my old blog – how long does *your* CHECKDB take to run? Well, I had a bunch of replies but I never got around to posting the results. I was reminded of this recently so threw together some graphs of the results. I've […]

Search Engine Q&A #10: Rebuilding Indexes and Updating Statistics

It seems like all I’ve been talking about on the forums the last couple of days is the correct order of operations in a maintenance plan. The biggest confusion seems to be about the effect of rebuilding an index on statistics, with some incorrect advice being given out on what to do. Rebuilding an index […]

Paul and Kimberly interview on RunAs Radio – What’s New in SQL Server 2008?

While we were in Barcelona we sat down with Richard Campbell and Greg Hughes from RunAs Radio to record a 1/2 hour interview on SQL Server 2008. We touch on a ton of different features (look at the number of Categories I’ve tagged this with!) and have a bunch of laughs along the way – […]

CHECKDB From Every Angle: Consistency Checking Options for a VLDB

This is a question that comes up a lot – how to run consistency checks on a VLDB? We’re talking hundreds of GBs or 1 TB or more. These databases are now common on SQL Server – there are many customers with multi-TB databases. Any experienced DBA knows the value of running consistency checks, even […]

CHECKDB From Every Angle: How long will CHECKDB take to run?

This is a subject I posted about last year on my old blog but it came up at SQL Connections last week several times so I want to repost it for those who’ve just started following my blog. There’s only one time when you should be trying to work out how long a CHECKDB is […]

Conference Questions Pot-Pourri #5: Database snapshots, and creating a snapshot of a single filegroup

One of the sessions we did yesterday at IT Forum was an Instructor-Led Lab on database snapshots, an Enterprise Edition feature of SQL Server 2005. Database snapshots are not particularly well known in the DBA community and there are many misconceptions about them. The session generated a bunch of questions, some of which I’ll answer […]

CHECKDB From Every Angle: Tips and tricks for interpreting CHECKDB output

Last week at SQL Connections someone said that CHECKDB’s output is ‘useless’.  Given that I wrote CHECKDB for SQL Server 2005 I was only mildly offended :-) But there’s a lot of truth in that statement – the error messages from CHECKDB do tell you exactly what’s corrupt in the database but they’re not exactly […]

Auto-shrink – turn it OFF!

I’m in the middle of a flight from Washington D.C. to Zurich on the way to Barcelona for TechEd IT Forum and I can’t sleep – Kimberly’s out like a light so what else is there to do except write another blog post? :-) OK – actually posting this from Barcelona on Tuesday before our […]

Conference Questions Pot-Pourri #1: Indexes, stats, corruption, and Enterprise-only features

As promised, here’s the first of the grab-bag of questions we were asked during conferences. I’m blogging a selection of the stuff I noted down. These are some of the questions we were asked during our pre-con at SQL Connections on Database Maintenance: From Planning to Practice to Post-Mortem. It was cool that people came prepared […]

Search Engine Q&A #8: How can defragging an index break synchronous mirroring? And what happens?

(Been a few days since I posted – had some real work to do :-) Today I'll post a few things from the queue that's been building up) This is part Q&A and part follow-on from my last post about running index maintenance when a database is mirrored. A customer has a maintenance plan that involves […]

More on Database Mirroring performance and index maintenance

There’s been some discussion over on the SQL Server Central forums about database mirroring performance, and one question in particular on how to do index rebuilds on mirrored VLDBs. Remember that in database mirroring, the database has to be in the full recovery mode, so all index rebuilds are fully logged. It all comes down […]

BACKUP LOG WITH NO_LOG – use, abuse, and undocumented trace flags to stop it

This has come up several times over the last few days, and is something that Kimberly and I discuss (and deplore) when we lecture – the use of BACKUP LOG WITH NO_LOG (or TRUNCATE_ONLY – they’re synonymous) to allow log truncation. How is it used? The common use is when the transaction log grows to […]

Indexes From Every Angle: How can you tell if an index is being used?

Whenever I’m discussing index maintenance, and specifically fragmentation, I always make a point of saying ‘Make sure the index is being used before doing anything about fragmentation’. If an index isn’t being used very much, but has very low page density (lots of free space in the index pages), then it will be occupying a […]

Search Engine Q&A #2: Moving a database while Database Mirroring is running

This was a question from the MSDN Disaster Recovery forum I started while I was at Microsoft. I have a 600 gig database that has a mirror.  I need to move the databases from local drives to a SAN.  Can anyone recommend a document that lists the steps to go through to move both the […]