So You've Got A Corrput Database

You get an urgent email from one of your users. He's getting a message when he tries to add a new record to a table that says: "A possible database consistency problem has been detected on database 'GOOFYDATABASE'. DBCC CHECKDB and DBCC CHECKCATALOG should be run on database 'GOOFYDATABASE'. What do you do?

a) Shrug your shoulders and say, "Well consistency is the hobgoblin of little minds anyway."
b) Shriek, "That's Utter Poppycock!"
c) Try, with your amazingly limited knowledge of SQL Server (amazing since you've been coding in Server for almost six years), to fix the thing.

If you picked c, maybe this will help more than the ridiculous Microsoft page with all of its inane commas, single quotes, and parentheses.

Assume for everything below that your database is named GOOFYDATABASE - the actual things to type are red.

Open up your query analyzer and type in this:

DBCC CHECKDB (GOOFYDATABASE) with ALL_ERRORMSGS
That is if you actually want to see the errors; if you're a genius and you don't need to see them, you can probably bypass this step. Hell, you can probably bypass this whole blog.

Now, you'll get a whole bunch of poppycock printed out and at the bottom it will tell you if you have any errors.

CHECKDB found 0 allocation errors and 2 consistency errors in database 'GOOFYDATABASE'.repair_allow_data_loss is the minimum repair level for the errors found by DBCC CHECKDB (GOOFYDATABASE ).DBCC execution completed. If DBCC printed error messages, contact your system administrator.

Now's the point where you go, hmmm, DBCC printed error messages and it says to contact your system administrator, who is in fact, you, so you should spend a few moments considering the best way to contact yourself: email, phone, telepathic conversations between your different personalities? After you figure out the best way to talk to yourself, search through the whole list of poppycock that DBCC printed out and find where the consistencies are. If there are errors in only a few tables, then I'd recommend just fixing the busted tables rather than fixing the whole, humongous database. To do that, first you have to put the database in single user mode.

You can do this one of two ways:

Method 1) Change it in Enterprise Manager by right-clicking the database you want to put in single-user mode. Click on the options tab. Click in the Restrict Access box to put a check in it. Click on the single user circle to put a dot in it (Yes, this is one of those pages that Microsoft developed to see how many geometric shapes they could get on one form). Click OK. And you're all set.

I don't recommend this method. Basically, because I was never good at geometry. And, you've just set the database into single-user mode, but if you're set up to log in automatically to Enterprise Manager, you have NO IDEA WHO THAT USER IS. And you can bet when you go back into Query Analyzer, it won't be the same user and you'll be hosed. So, it's just easier to do it by method 2.

Method 2) Open your query analyzer and type the following:

ALTER DATABASE GOOFYDATABAE SET SINGLE_USER

Now, you're in single user mode and you can get to work fixing the all the corruption (well, at least in the database)

If you have a few tables to fix, type the following for each table (assume the table name here is TABLE_GOOFY):

DBCC CHECKTABLE (TABLE_GOOFY, REPAIR_REBUILD) with ALL_ERRORMSGS

Now, understand that this may or may not work. You may get a message telling you that the error was found, but couldn't be fixed with the repair method that you selected. My guess is that the error is so ugly that SQL can't fix it without deleting the data in the corrupt record. But, to me, it makes sense to at least try this so if you can save all the data, you do. When it doesn't work, then type in this:

DBCC CHECKTABLE (TABLE_GOOFY, REPAIR_ALLOW_DATA_LOSS) with ALL_ERRORMSGS

With a little luck, when you run this the error will be fixed (as if by magic) and then you'll be good to go. But, before you leave, make sure you type this:

ALTER DATABASE GOOFYDATABASE SET MULTI_USER

so you won't shoot the feet off of all the other people trying to access the database with another username (say, oh, the Enterprise Manager).

Then, go test the thing out, zip it up and send it back out to your user, who will be beyond ecstatic and can't believe that you've given them back their beloved database, whole and complete again. Or not..