LLI Wiki — My Digital Knowledge Base

Recover a SUSPECT database

Steps to recover a SUSPECT database: EMERGENCY mode, CHECKDB, single-user mode, repair, and back to multi-user.

A database in SUSPECT state is not accessible. SQL Server sets this state when recovery of the database fails, for example because of a corrupted transaction log. The steps below put the database in EMERGENCY mode, check its consistency, then try a repair.

For the meaning of each database state, see the SQLShack article.

Run the statements from the master database, with a login that is a member of the sysadmin role.

Steps

  1. Clear the SUSPECT flag. The database stays inaccessible until it is recovered.
  2. Set the database to EMERGENCY mode: it becomes read-only, and only members of the sysadmin role can access it.
  3. Check consistency.
  4. Set single-user mode. Open transactions are rolled back.
  5. Repair all reported errors. Data loss is possible.
  6. Set multi-user mode.
  7. Check consistency again.
-- 1. Clear the SUSPECT flag
EXEC sp_resetstatus 'AppDb';

-- 2. EMERGENCY mode (read-only, sysadmin only)
ALTER DATABASE AppDb SET EMERGENCY;

-- 3. Check consistency
DBCC CHECKDB ('AppDb');

-- 4. Single-user mode, open transactions rolled back
ALTER DATABASE AppDb SET SINGLE_USER WITH ROLLBACK IMMEDIATE;

-- 5. Repair all reported errors
DBCC CHECKDB ('AppDb', REPAIR_ALLOW_DATA_LOSS);

-- 6. Multi-user mode
ALTER DATABASE AppDb SET MULTI_USER;

-- 7. Check consistency again
DBCC CHECKDB ('AppDb');

Warning: REPAIR_ALLOW_DATA_LOSS repairs the reported errors but can cause data loss. Use it as a last resort, when the other options have failed.

Mastodon LLIMastodon SocialFosstodon