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
- Clear the SUSPECT flag. The database stays inaccessible until it is recovered.
- Set the database to EMERGENCY mode: it becomes read-only, and only members of the sysadmin role can access it.
- Check consistency.
- Set single-user mode. Open transactions are rolled back.
- Repair all reported errors. Data loss is possible.
- Set multi-user mode.
- 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_LOSSrepairs the reported errors but can cause data loss. Use it as a last resort, when the other options have failed.