I was running a resource-intensive reindexing script when my CPU froze for about 15 mins without any sign of “thawing”. Being an impatient idiot, I reached for the power button to reboot.
Back to the SSMS….. aaaand the database has gone suspect, GREAT! *with sarcasm*
Well, since this is a non-production database, there’s no issue there. I could just restore any backup copy and I’d be off and running in no time. But I wanted to test if it’s possible to repair (I don’t come across a suspect database too often).
My Google research led me to various articles, which I can summarise as the following:
Run: DBCC CHECKDB (‘DB_NAME’) WITH NO_INFOMSGS, ALL_ERRORMSGS
This gives me the following error:
Msg 926, Level 14, State 1, Line 1
Database ‘DB_NAME’ cannot be opened. It has been marked SUSPECT by recovery. See the SQL Server errorlog for more information.
Msg 926 tells you that the database is suspect but it is still online (http://support.microsoft.com/kb/926070). To check:
SELECT state_desc FROM sys.databases WHERE name=’DB_NAME’
Make sure that you do a backup by now in case the repair caused some irretrievable errors.
Moving along: turn off the suspect flag
EXEC sp_resetstatus ‘DB_NAME’;
ALTER DATABASE DB_NAME SET EMERGENCY
DBCC CHECKDB (‘DB_NAME’)
Msg 7984, Level 16, State 1, Line 1
System table pre-checks: Object ID 4. Page (1:2048) has unexpected page type 2. Check statement terminated due to unrepairable error.
DBCC results for ‘DB_NAME’.
CHECKDB found 0 allocation errors and 0 consistency errors in database ‘DB_NAME’.
Unrepairable error! Not a good sign! Still tried to repair anyway….
ALTER DATABASE DB_NAME SET SINGLE_USER WITH ROLLBACK IMMEDIATE
DBCC CHECKDB (‘DB_NAME’, REPAIR_ALLOW_DATA_LOSS)
Warning: The log for database ‘DB_NAME’ has been rebuilt. Transactional consistency has been lost. The RESTORE chain was broken, and the server no longer has context on the previous log files, so you will need to know what they were. You should run DBCC CHECKDB to validate physical consistency. The database has been put in dbo-only mode. When you are ready to make the database available for use, you will need to reset database options and delete any extra log files.
Msg 7984, Level 16, State 1, Line 1
System table pre-checks: Object ID 4. Page (1:2048) has unexpected page type 2. Check statement terminated due to unrepairable error.
DBCC results for ‘DB_NAME’.
CHECKDB found 0 allocation errors and 0 consistency errors in database ‘DB_NAME’.
Now set everything back to normal:
ALTER DATABASE DB_NAME SET MULTI_USER
ALTER DATABaSE DB_NAME SET ONLINE
Another DBCC CHECKDB confirmed that the metadata was corrupt, along with invalid IAM_PAGE page header values.
Msg 8946, Level 16, State 3, Line 1
Table error: Allocation page (1:177) has invalid IAM_PAGE page header values. Type is 2. Check type, alloc unit ID and page ID on the page.
Msg 608, Level 16, State 1, Line 1
No catalog entry found for partition ID 72057594070106112 in database 6. The metadata is inconsistent. Run DBCC CHECKDB to check for a metadata corruption.
OK, there’s a chance as slim as a pin to get this database back to normal (without screwing anything else). I think I will just restore from that back up copy….
The moral of the story:
Always keep a backup. If you find yourself without one, do your best to save what’s left. Oh, and don’t reach the power button too soon!
Highly Recommended:
Great post!