Corrupt test DB with no Backup -going for the last resort
Today I was notified by a coworker that a database running on a VM in our test lab was acting up and that the event-log was filled with error messages like these:
____________________________________________________________
“SQL Server detected a logical consistency-based I/O error: incorrect checksum (expected: 0xdadadada; actual: 0×6d6d6d6d). It occurred during a read of page (1:4232) in database ID 5 at offset 0×00000002110000 in file ‘C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\DATA\databaseName.mdf’. Additional messages in the SQL Server error log or system event log may provide more detail.
This is a severe error condition that threatens database integrity and must be corrected immediately. Complete a full database consistency check (DBCC CHECKDB). This error can be caused by many factors; for more information, see SQL Server Books Online.”
____________________________________________________________
pretty scary stuff If you ask me..
So the first thing I tried was to start query analyser and run
DBCC CHECKDB (‘databaseName’)
That however only gave me this error message.
____________________________________________________________
Msg 8967, Level 16, State 216, Line 1
An internal error occurred in DBCC that prevented further processing.
Contact Customer Support Services.
____________________________________________________________
So – doing some searches on the web I stumbled across a couple of sql forums that advised to restore from backup.. well – since the DB in question was used in a lab for development, I have to admit to not having a resent backup at hand. This DB was not used for any System Center product. We could recreate the DB from scripts but I wanted to make an attempt at fixing it first.
-The command I will be using will delete the corrupt parts of the DB so be aware this is a last resort solution, If you do have a backup – use that to restore the DB…
So what I did was this:
I first made a backup of the database and put it on another server.
I then ran chkdsk /f on all drives on the server – rebooting to allow for checking the system drive.
After the server came up I put the db into single-user mode ( right click db\properties\options\restrict access= “single user”
I then used this command
DBCC CHECKDB (databaseName, REPAIR_ALLOW_DATA_LOSS)
It reported that it had fixed some but not all of the errors so I ran it again..
This time I got no errors.
Last i ran DBCC CHECKDB (‘databaseName’) once again – this time with noe errors.
I put the database back into multi-user mode (same procedure as last time ( close queries first))
I then rebooted the server for good measure ..
Everything now looks fine – Now I’m just curious to see if any issues might arise as a result off the corrupt data that’s been deleted.



