A SQL Server database can enter suspect mode for several reasons: the device hosting the data or log files went offline, the files became unavailable or corrupted, or the server shut down uncleanly mid-transaction. When this happens the database is inaccessible and SQL Server marks it as suspect to prevent further damage.
This post walks through the six steps to bring a suspect database back online. The examples use a database named test; substitute your own database name throughout.
REPAIR_ALLOW_DATA_LOSS, which, as the name states, may discard data to resolve corruption. If you have a recent backup, restoring from it is always the safer path.
sp_resetstatus clears the suspect and read-only bits in the database status flags without altering any other database state. This is a prerequisite before any of the subsequent ALTER DATABASE commands will succeed.
EXEC sp_resetstatus 'test'Emergency mode makes the database read-only and restricts access to members of the sysadmin fixed server role only. This lets you inspect and repair the database without other connections interfering during the process.
ALTER DATABASE test SET EMERGENCYRun DBCC CHECKDB to get a full picture of the corruption before attempting any repair. The output will tell you which objects are affected and whether the damage can be repaired with or without data loss. Read this output carefully before proceeding to step 5.
DBCC CheckDB ('test')Repair operations require exclusive access to the database. WITH ROLLBACK IMMEDIATE terminates any existing connections and rolls back their open transactions immediately rather than waiting for them to complete.
ALTER DATABASE test SET SINGLE_USER WITH ROLLBACK IMMEDIATEThis command instructs SQL Server to repair all errors it found during the CHECKDB run. The REPAIR_ALLOW_DATA_LOSS option is the most aggressive repair level and will deallocate corrupt pages if necessary to make the database consistent again.
DBCC CheckDB ('test', REPAIR_ALLOW_DATA_LOSS)REPAIR_ALLOW_DATA_LOSS is a last resort. Any pages that cannot be repaired in place will be discarded, meaning rows or entire tables could be permanently lost. If the CHECKDB output in step 3 showed that a less destructive repair level (REPAIR_REBUILD) is sufficient for your errors, use that instead.
Once the repair completes without error, lift the single-user restriction so normal application connections can resume.
ALTER DATABASE test SET MULTI_USERThe full six-step sequence in one place, ready to adapt with your database name:
EXEC sp_resetstatus 'test'
ALTER DATABASE test SET EMERGENCY
DBCC CheckDB ('test')
ALTER DATABASE test SET SINGLE_USER WITH ROLLBACK IMMEDIATE
DBCC CheckDB ('test', REPAIR_ALLOW_DATA_LOSS)
ALTER DATABASE test SET MULTI_USERAfter bringing the database back online, run a full DBCC CHECKDB one more time without a repair option to confirm there are no remaining consistency errors. Follow that with a full database backup so you have a clean recovery point before the database returns to production use.
