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_LOSScan discard data to repair the database. Restore from a backup when one is available, and copy the database files before running a repair.
Procedure
- Set the database to emergency mode:
EXEC sp_resetstatus 'SuspectDB';
ALTER DATABASE SuspectDB SET EMERGENCY;
- Check the database for errors. This step does not repair anything:
DBCC CHECKDB (SuspectDB);
DBCC CHECKCATALOG (SuspectDB);
- 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;
- 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);
- 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
- Set the database back to multi-user mode:
ALTER DATABASE SuspectDB SET MULTI_USER;
-
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.
-
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;