What do you do when you come across an error like this?

 

 

You look around and check if anybody else looks like they want their day ruined. I’m betting not, so it’s your problem. First thing is to check that you have DBCC CHECKDB running regularly and set up to email out when errors occur so you can jump on it right away, you do have those running right? If so, excellent, get checking which was your last good backup and restore that. Append your new data since that date and you’re perfect.

If not, this is going to hurt.

First you need to see what you’re working with. Run DBCC CHECKDB on the database that’s corrupted and check the extent of your issue, be careful, this is a very IO intensive operation and will have a serious performance impact. If the corruption is in non clustered indexes then you can just rebuild said indexes, run CHECKDB again and make sure it’s fixed your corruption issues.

In the example here, we’ve got clustered indexes that are corrupt. This is a little more difficult to fix.

Table error: page (1:24441410) allocated to object ID 2025058250, index ID 1
CHECKDB found 2301 allocation errors and 34 consistency errors in database 'CorruptedDatabaseName'.

Now all is not yet lost, there are a couple of steps to take. You can try running DBCC CHECKDB with REPAIR_REBUILD check out books online. This is going to take longer than a normal CHECKDB but definitely worth it. It will show how many errors were fixed, if this does it then great, close this webpage and go back to your real job.

In this example, it didn’t work. Now, the next option is going to result in data loss. You want to make sure you have a full backup before you run this and you want to run it inside a transaction before committing so that you can rollback if you need to. DBCC CHECKDB REPAIR_ALLOW_DATA_LOSS is going to try to do it’s job and fix the corruption in your database. Again, this is going to hammer your IO but at this point you’re probably just focused on fixing this corruption.

CHECKDB found 2301 allocation errors and 1882 consistency errors in database 'CorruptedDatabaseName'.
CHECKDB fixed 2301 allocation errors and 0 consistency errors in database 'CorruptedDatabaseName'.

As you can see, the corruption wasn’t fixed by this. This means that we’re going to have to take really drastic action on this one. Fortunately in this scenario this database only has 10 tables. Ultimately the way this was fixed was by taking an empty copy of the corrupt database, transferring the data over to our new database and deleting the original. Yes, data loss was a sad fact in this but there wasn’t another way.

Conclusion: corruption fixed