LLI Wiki — My Digital Knowledge Base

Recover a suspect database with DBCC CHECKDB repair options

Put a suspect SQL Server database in emergency mode, run the DBCC CHECKDB repair options in single-user mode, then return it to multi-user mode.

A database marked as suspect cannot be used until its consistency is checked and, when possible, repaired. The procedure below puts the database in emergency mode, checks it, repairs it in single-user mode, and returns it to multi-user mode. It summarises the public articles linked in the references.

Warning: REPAIR_ALLOW_DATA_LOSS can discard data to repair the database. Restore from a backup when one is available, and copy the database files before running a repair.

Procedure

  1. Set the database to emergency mode:
EXEC sp_resetstatus 'SuspectDB';
ALTER DATABASE SuspectDB SET EMERGENCY;
  1. Check the database for errors. This step does not repair anything:
DBCC CHECKDB (SuspectDB);
DBCC CHECKCATALOG (SuspectDB);
  1. Set the database to single-user mode. This rolls back the most recent open transactions and prevents other users from connecting during the repair:
ALTER DATABASE SuspectDB SET SINGLE_USER WITH ROLLBACK IMMEDIATE;
  1. Run the repair. Use the option that matches the errors found in step 2:
Option Purpose
REPAIR_FAST Quick fix
REPAIR_REBUILD Reset the indexes
REPAIR_ALLOW_DATA_LOSS Correct missing data, which can discard data
DBCC CHECKALLOC ... REPAIR_REBUILD Fix allocation problems
DBCC DBREINDEX ... REPAIR_REBUILD Fix index problems
DBCC CHECKDB (SuspectDB, REPAIR_FAST);
DBCC CHECKDB (SuspectDB, REPAIR_REBUILD);
DBCC CHECKDB (SuspectDB, REPAIR_ALLOW_DATA_LOSS);
DBCC CHECKALLOC (SuspectDB, REPAIR_REBUILD);
DBCC DBREINDEX (SuspectDB, REPAIR_REBUILD);
  1. If errors remain after the repair, run the data-loss repair again. The source suggests up to ten runs:
DECLARE @Number INT = 1;
WHILE @Number <= 10
BEGIN
    PRINT @Number;
    SET @Number = @Number + 1;
    DBCC CHECKDB (SuspectDB, REPAIR_ALLOW_DATA_LOSS);
END
  1. Set the database back to multi-user mode:
ALTER DATABASE SuspectDB SET MULTI_USER;
  1. Take a backup right away, because the database is now in a usable state. If the database is on a virtual machine, take a snapshot at the same time. The source also suggests restarting the server and checking the database again.

  2. Check the data and look for the remaining errors:

DBCC CHECKDB (SuspectDB);

If errors remain, the source suggests taking a backup and restoring it, which may solve this part of the problem.

If other connections block single-user mode

Other sessions can connect when the database is in single-user mode. List the sessions connected to the database, then end the unwanted ones:

SELECT d.name, d.dbid, spid, login_time, nt_domain, nt_username, loginame
FROM sysprocesses p INNER JOIN sysdatabases d ON p.dbid = d.dbid
WHERE d.name = 'SuspectDB';
KILL 51;

References

Mastodon LLIMastodon SocialFosstodon