Q: How can I repair database corruption as quickly as possible if I don’t have workable backups? And after repairing some database corruption, my database shrank by 100GB. How is that possible?
A: As further background, the reader experienced an I/O subsystem problem and ran DBCC CHECKDB, which wasn’t being run regularly. The results from DBCC CHECKDB indicated database corruption, so the backups were examined. It became apparent that the backups contained corruption, too. There are two operational problems here:
- No regular consistency checks
- No regular verification of backup integrity
If you’re in this situation, I urge you to read my blog posts “Importance of running regular consistency checks” and “Importance of validating backups.”
As for the reader’s question, if you have to run REPAIR_ALLOW_DATA_LOSS, the database must be in SINGLE_USER mode (essentially offline), so it makes sense to limit the amount of time that DBCC takes to run if applicable. I recommend attempting this only if the only corruptions reported are allocation corruptions or the corruptions are confined to a single table. These types of corruptions are unlikely to be masking further corruptions, and therefore are less likely to require a second repair.
If the only corruptions are allocation corruptions, you can use
DBCC CHECKALLOC
(yourdb, REPAIR_ALLOW_DATA_LOSS)
WITH ALL_ERRORMSGS, NO_INFOMSGS
If the corruptions are confined to a single table, you can use the following commands:
DBCC CHECKTABLE
(corrupttable, REPAIR_ALLOW_DATA_LOSS)
WITH ALL_ERRORMSGS, NO_INFOMSGS
After running each of these commands, you need to run DBCC CHECKDB again to ensure that no more corruption was uncovered (because a corruption might be preventing DBCC from checking a portion of the database). To limit the downtime of the application, you can take the database out of SINGLE_USER mode to run DBCC CHECKDB to ensure there are no further corruptions. I don’t recommend running REPAIR again because you’ll want to see what the corruptions are before deciding on the best course of action.
In this case, the reader who sent me this question noticed that after running DBCC CHECKALLOC to repair the corruptions he had found, the database seemed to shrink by almost 100GB later that day. How could that have happened?
The only way that a database file can change size is if a shrink operation occurs. It turns out that he had auto-shrink enabled on the database. When the allocation repairs were done, an allocation bitmap was deallocated toward the end of the database file, so the next time auto-shrink ran, it was able to shrink a large amount of space from the data file.
This is another operational problem—auto-shrink should never be enabled because of the resources it consumes and the index fragmentation it creates. See my Shrink blog post category for more details.
