SQLServerPedia

So first off I made the “tempdb” group in SQLBatman’s rankings which means I’m a blogger “that is on his radar screen right now”.   He didn’t say whether or not it was a good radar screen or not but I’m in there with Jeremiah Peschka and Kendal Van Dyke so I’ll take it as a compliment for sure.

Enough touchy feely stuff… let’s get to the guts of the post…

OK Chicken Little your database is corrupt and your sky is falling.  You want this dirty feeling gone as quickly as possible.  Before you go and do something crazy like blindly running a repair or detaching a suspect database take some time to do some investigation work to see if you can figure out what is going on.  At the end of the day you normally have 2 options… try and repair the damage or restore the database from backup. In order to determine which path is best for you you should do some things like running DBCC CHECKDB and looking in the Error Log to try and give yourself some clues as to the extent of the problem.  Check out the Windows Event Log, are there any RAID or disk errors? In the result set of your DBCC CHECKDB you might get 1 error, you might get 100 errors, or it might not even finish if your db is in a really bad state.  If DBCC CHECKDB can’t run at all then you’re pretty well looking at recovering from backup. There are some other errors in the CHECKDB output that would usually force you to go back to a backup, things like data purity errors or page header corruption are a couple examples.

An “easy” thing that can come out of your CHECKDB output is if a nonclustered index is corrupt.  If the indexid is greater than 1 you can rebuild it offline in SQL Server 2005 or recreate it in SQL Server 2008 and Bob’s your uncle.  Now this doesn’t tell you WHY this happened but it will resolve your corrupt index and (hopefully) get you back into the game. 

Another example of a “repairable” problem is a corrupt page(s).  If you have corrupt pages your first thought could be to try and do a single page restore using a full backup and then applying the subsequent transaction log backups.  What if you only do nightly full backups of your database?  An option in this case is the REPAIR_ALLOW_DATA_LOSS argument in your DBCC CHECKDB statement.  This is a well named argument because this can/will cause rows in your table(s) to be removed in order to attempt to fix the corruption.  Always be sure to do a full backup BEFORE using the REPAIR_ALLOW_DATA_LOSS option just to be sure you can get back to where you are now if something goes wrong.  This option also doesn’t care about constraints so be sure to run a DBCC CHECKCONSTRAINTS command after the REPAIR_ALLOW_DATA_LOSS.

Anyways at the end of the day corruption happens… do what you can through alerts and scheduled running of DBCC CHECKDB to try and capture/isolate the corruption as soon as possible in order to minimize damage and impact to the business. 

One final thought… even though you may think that you fixed the problem(s) ALWAYS run a final CHECKDB just to make sure that you’ve really saved the world (or at least your database) from corruption.

Here is the Microsoft Books Online for DBCC CHECKDB make sure to give this a read (twice) before acting.

Enjoy!!