(The Curious Case of… used to be part of our bi-weekly newsletter but we decided to make it a regular blog post instead so it can sometimes be more frequent. It covers something interesting one of us encountered when working with a client, doing some testing, or were asked in a random question from the community.)

I had a blog comment question a few days ago that asked why emergency-mode repair requires the database to be in EMERGENCY mode as well as SINGLE_USER mode.

All repair operations that DBCC CHECKDB (and related commands) performs require the database to be in single-user mode so there’s a guarantee that nothing can be changing while the checks and repairs are done. But that doesn’t change the behavior of what repair does – that needs emergency mode too.

Emergency mode is internally known as ‘bypass recovery’ mode, and is used when a database hasn’t had crash recovery run on it, because of corruption issues or a missing or damaged log file. It allows access to the database in the unrecovered state, which may mean the database has inconsistencies in its structures and/or data because of in-flight transactions that have not been rolled back (as recovery couldn’t run).

When a database is in emergency mode, it’s not possible to create a database snapshot (as that involves running recovery) and so a DBCC CHECKDB without a repair option simply treats the database as if it’s read-only, and runs the consistency checks. When a repair option *is* specified (and only REPAIR_ALLOW_DATA_LOSS is permitted) in emergency mode, that tells DBCC CHECKDB that the drastic, last-resort emergency mode repair should be performed. This will:

  • Run as much crash recovery as possible, skipping errors, unlike real crash recovery which will fail when it encounters an error
  • Delete the transaction log
  • Create a new transaction log
  • Run a regular REPAIR_ALLOW_DATA_LOSS check and repair
  • Bring the database online if possible, albeit maybe with corruptions still in the database

So emergency mode isn’t single-user mode, and vice versa – they’re both required for an emergency-mode repair to run.

You can read more about emergency-mode repair in my blog post EMERGENCY-mode repair: the very, very last resort.