I have a little database that I don’t back up, nor do I do any maintenance on it. I don’t even check it for corruption. This database is what I would consider a “throw-away” database. It would be faster to have the application recreate the DB from scratch than it would be to restore it from backup, and it doesn’t hold any permanent information. In fact, the only time it is of any use to me is when I’m actively using the application, and information is only retained for an hour. So it has really limited usage.
With that in mind, imagine my delight when this database became corrupted. “Yay! A corrupt DB to play with!” SSMS showed the database status as SUSPECT. Like any good DBA, the first thing I did was take a backup. You know how to do that, right?
BACKUP DATABASE igrep TO 'F:\igrep_corrupt.bak' WITH CONTINUE_AFTER_ERROR; GO
Having saved off a copy of that DB, I placed the database in emergency mode, so that I could run a full consistency check with this:
ALTER DATABASE igrep SET EMERGENCY; GO DBCC CHECKDB(igrep) WITH NO_INFOMSGS; GO
The final tally was 5 allocation errors, and 39 consistency errors. In other words, this DB was a mess. Since there was no useful data to lose at this point, I decided to go ahead and run a repair to see what it would do.
DBCC CHECKDB(igrep, repair_allow_data_loss) WITH NO_INFOMSGS; GO
Msg 7919, Level 16, State 3, Line 1 Repair statement not processed. Database needs to be in single user mode.
Oops. Forgot that when in EMERGENCY mode, you’re in restricted to syadmins only, but not in SINGLE_USER.
ALTER DATABASE igrep SET SINGLE_USER; GO
Hmmm… no error messages on that one. Just for grins and giggles…
ALTER DATABASE igrep SET ONLINE; GO
Msg 926, Level 14, State 1, Line 1 Database 'igrep' cannot be opened. It has been marked SUSPECT by recovery. See the SQL Server errorlog for more information. Msg 5069, Level 16, State 1, Line 1 ALTER DATABASE statement failed. Msg 3414, Level 21, State 2, Line 1 An error occurred during recovery, preventing the database 'igrep' (database ID 10) from restarting. Diagnose the recovery errors and fix them, or restore from a known good backup. If errors are not corrected or expected, contact Technical Support.
Ok, wasn’t expecting success anyway. :-) Back to emergency mode. This time, we’ll run the repair as well.
ALTER DATABASE igrep SET EMERGENCY; GO DBCC CHECKDB(igrep, repair_allow_data_loss) WITH NO_INFOMSGS; GO
Tick tock, tick tock, tick tock, DING! About 10 minutes later… here’s what DBCC reported back:
CHECKDB found 5 allocation errors and 70 consistency errors in database 'igrep'. CHECKDB fixed 2 allocation errors and 70 consistency errors in database 'igrep'.
Woot! We’re complete! In order to fix the consistency errors, the following things happened:
* Some pages were deallocated (buh-bye, data)
* Some extents were marked as allocated to specific objects.
* Specific clustered and non-clustered indexes were rebuilt.
The other interesting thing is, note that the number of consistency errors went *up* when the allocation errors were fixed. It’s likely that…
Hold up… All the consistency errors were fixed, but only two of five allocation errors? Let’s scroll up a bit. Near the top, we find the following:
Msg 8905, Level 16, State 1, Line 2 Extent (1:682840) in database ID 10 is marked allocated in the GAM, but no SGAM or IAM has allocated it. Could not repair this error. Msg 8905, Level 16, State 1, Line 2 Extent (1:682880) in database ID 10 is marked allocated in the GAM, but no SGAM or IAM has allocated it. Repairing this error requires other errors to be corrected first. Msg 8905, Level 16, State 1, Line 2 Extent (1:682952) in database ID 10 is marked allocated in the GAM, but no SGAM or IAM has allocated it. Repairing this error requires other errors to be corrected first.
Interestingly enough, SQL has marked the database as ONLINE. I thought that would denote a successful repair, but DBCC says once again that repair_allow_data_loss is the minimum repair level for this database. Running CHECKDB again, we get three of these:
Msg 8905, Level 16, State 1, Line 1 Extent (1:682952) in database ID 10 is marked allocated in the GAM, but no SGAM or IAM has allocated it.
Let’s run the same repair again. This time, we get this:
CHECKDB found 3 allocation errors and 20 consistency errors in database 'igrep'. CHECKDB fixed 3 allocation errors and 20 consistency errors in database 'igrep'.
So fixing the allocation errors caused more consistency errors, again. Not surprising, when you think about it. Allocating and deallocating pages, especially when foreign keys are in play, is bound to cause some structural inconsistency. That’s why it’s paramount that after running a repair of any kind, you run another CHECKDB to see if the repair caused any other damage. Speaking of which – after this last repair, CHECKDB finished with no errors, and the application’s service started fine. The applicatoin itself started working again, and all was well. Keep in mind that this was the *least* likely outcome. I didn’t do a good job, or even do the right things, here – I got *extremely* lucky.
So what are the big ideas we take away from all this?
- Backups are your friend. Take them, test them, and store them safely.
- By default, try a restore before you try repair. The former is clean, the latter can get *really* messy.
- If you don’t have a backup, take one with CONTINUE_AFTER_ERROR before doing *anything* else.
- Emergency mode can bring up otherwise inaccessible databases, sometimes. Remember that to do a repair, though, you’ll still have to set it to SINGLE_USER mode
- Just because repair finished, doesn’t mean you’re good to go. Check and re-check your database, both before and after repair.
Anything else I missed?