Tip: Know How and When to Use Emergency Mode Repair
EMERGENCY mode is used when the transaction log for the database has been damaged and no backups are available to restore from. In this case, regular repairs do not workârepairs are fully logged and this cannot occur if the transaction log is damaged.
In SQL Server 2000 and earlier, EMERGENCY mode was undocumented and was used to allow the transaction log to be rebuilt using the undocumented DBCC REBUILD_LOG command. Unfortunately, this procedure became publicized on the Internet but usually without all necessary steps. For this reason, the dev team decided to add a documented and supported method of rebuilding a transaction log and recovering the database in SQL Server 2005. The feature is called emergency mode repair and its mechanism is unchanged for SQL Server 2008.
When the database is in EMERGENCY mode and SINGLE_USER mode, and DBCC CHECKDB is run with the REPAIR_ALLOW_DATA_LOSS option, the following steps are taken:
1. Force recovery to run on the transaction log (if it exists).
This is essentially recovery with CONTINUE_AFTER_ERROR, in a similar vein to using CONTINUE_AFTER_ERROR with either BACKUP or RESTORE. The idea behind this is that the database is already inconsistent because either the transaction log is corrupt or something in the database is corrupt in such a way that recovery cannot complete.
Given that the database is inconsistent and the transaction log is about to be rebuilt, it makes sense to salvage as much transactional information as possible from the log before it is discarded and a new one is created.
This recovery with CONTINUE_AFTER_ERROR functionality is possible only from within DBCC CHECKDB.
2. Rebuild the transaction log if it is corrupt.
3. Run the full set of consistency checks on the database with the REPAIR_ALLOW_DATA_LOSS option.
4. Bring the database online.
From the upcoming release from Microsoft Press, Microsoft SQL Server 2008 Internals (by Kalen Delaney, Paul S. Randal, Kimberly L. Tripp, Conor Cunningham, Adam Machanic, and Ben Nevarez) Due to be released on February 18, 2009.
You can find more information on EMERGENCY mode repair in Paul's blog post CHECKDB From Every Angle: EMERGENCY mode repair - the very, very last resort
Looking for More Tips?
For more SQL Server Tips, visit the TechNet Magazine SQL Server Tips page.
For more Tips on other products, visit the TechNet Magazine Tips index.