February 28, 2008 at 8:33 am
I ran DBCC CheckDB and it returned an error for one of our tables that has a varbinary(max) column. My question is what is the best way to fix the errors on a live production database. I noticed that to run DBCC CheckDB with any of the repair options requires the database to be put into single user mode. Is that my only option now? Also, if that is my only option, is it going to hurt the database more to wait until tonight to run DBCC CheckDB with repair? I'm also wondering if I could try to reorganize the index first? Any help would be greatly appreciated!
Thanks!
Here are some of the lines of errors:
Msg 8928, Level 16, State 1, Line 1
Object ID 176144935, index ID 1, partition ID 72057594294960128, alloc unit ID 72057602978217984 (type LOB data): Page (1:190513) could not be processed. See other errors for details.
Msg 8939, Level 16, State 98, Line 1
Table error: Object ID 176144935, index ID 1, partition ID 72057594294960128, alloc unit ID 72057602978217984 (type LOB data), page (1:190513). Test (IS_OFF (BUF_IOERR, pBUF->bstat)) failed. Values are 12716041 and -4.
Msg 8928, Level 16, State 1, Line 1
Object ID 176144935, index ID 1, partition ID 72057594294960128, alloc unit ID 72057602978217984 (type LOB data): Page (1:344577) could not be processed. See other errors for details.
Msg 8939, Level 16, State 98, Line 1
Table error: Object ID 176144935, index ID 1, partition ID 72057594294960128, alloc unit ID 72057602978217984 (type LOB data), page (1:344577). Test (IS_OFF (BUF_IOERR, pBUF->bstat)) failed. Values are 12716041 and -4.
Msg 8928, Level 16, State 1, Line 1
Wendy Schuman
February 28, 2008 at 8:41 am
Run the DBCC CHECKDB with repair option to get rid of those errors.
Cheers,
Sugeshkumar Rajendran
SQL Server MVP
http://sugeshkr.blogspot.com
February 28, 2008 at 8:44 am
Hi Sugesh,
Thanks for the reply. I noticed that I had to run dbcc checkdb with the repair options but then the db has to be put into Single User Mode. Will it become more of a problem if I wait for another 8 - 12 hourse before I run it or should I take the database down and do it now?
Thanks,
Wendy
Wendy Schuman
February 28, 2008 at 8:51 am
Sugesh Kumar (2/28/2008)
Run the DBCC CHECKDB with repair option to get rid of those errors.
Rather don't. Repair should be the last resort for fixing corruption, not the first.
The best way to get rid of database corruption is to restore a backup that was taken before the corruption, then apply the tran log backups.
Repair (when run with allow_data_loss) will cause loss of data.
Read these articles carefully:
Common bad advice around disaster recovery
Corruption: Last resorts that people try first...
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
February 28, 2008 at 8:57 am
p.s. You should also try and work out why the corruption occurred. Did you have a server crash? Are you gtting any errors from the hard drives? etc
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
February 28, 2008 at 11:26 am
Yes checkdb with repair puts the databse in single user mode. you need to plan for a downtime and do that. Also remember that restoring a database from a corrupted file will end in failure. so as Gail said if you go for restoration then do it with a backup file before the corruption.
Cheers,
Sugeshkumar Rajendran
SQL Server MVP
http://sugeshkr.blogspot.com
February 28, 2008 at 12:51 pm
In addition repair "with_allow_data_loss" may NOT repair and may even get things worse!!!
* Noel
February 28, 2008 at 10:31 pm
Yup. I've seen that before.
Had an old DB that we found corruption in. Had no idea how long it had been there, and the earliest backup we had was corrupted (SQL 2000)
Each time we ran repair, it left us with more errors than we started with. Eventually had to DTS out the data we could and recreate.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
February 29, 2008 at 7:38 am
Unfortunately, we do not know how long this corruption has been there or what caused it. The most recent backup that we used to restore also had corruption and we can't go back any farther and lose more data. The corruption is limited to one table in the database. I restored the corrupt database to a test database then ran the repair with allow data loss on it and that appears to have fixed the issues. Then I ran a query to find the records in the table in the production database that weren't in the fixed database. I'm hoping we can manually fix the records that are missing. Could there be more damage that I'm not aware of at this point?
This database has just recently moved to production. After we run the repair allow data loss on it, we are going to implement a maintenance plan to run checkdb nightly, which up to this point has not been done on this database. I'm hoping that once we implement that maintenance plan, if this happens again we will be able to see that an error occurred in the logs or event viewers.
If we are running checkdb nightly, should we do that before or after we execute the backups and are there any other dbcc checks that I should be doing?
I appreciate everyone's help on this. Thanks!
Wendy Schuman
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply