How to Recover SQLServer Database from Suspect Mode

Why SQL Database goes into Suspect Mode?

The database may go into suspect mode due to one or more of the following reasons:

  • The system's hard disk where database is stored is out of space.
  • The database file is corrupted or damaged.
  • The primary file group of the database is corrupted or missing.
  • The transaction log file is inaccessible or corrupted.
  • MS SQL server shuts down or crashes in the middle of a transaction.
  • The database is terminated unexpectedly.
  • SQL is unable to complete a rollback or roll-forward operation.

Solutions to Recover SQL Database from Suspect Mode

Method 1: Restore Database from Backup

RESTORE DATABASE databasetest13
FROM DISK = 'Z:\SQLServerBackups\databasetest13.bak';

Method 2: Use DBCC CHECKDB Command to Repair the Database

It is suggested to create a backup of the SQL database before running this command.

--Turn off the suspect flag on the database and set it to EMERGENCY mode.
USE master;
EXEC sp_resetstatus 'DBName';
ALTER DATABASE DBName SET EMERGENCY;

--Check the integrity/consistency of the database.
DBCC CHECKDB('DBName')
 
--If report the consistency errors (if found) in the database will recommend the repair option.
ALTER DATABASE DB_Name SET SINGLE_USER WITH ROLLBACK IMMEDIATE;
ALTER DATABASE DB_Name SET ONLINE;

DBCC CHECKDB('DBName', REPAIR_FAST);            /* Maintains syntax for backward compatibility only. No repair actions are performed. */
DBCC CHECKDB('DBName', REPAIR_REBUILD);         /* This option can quickly repair missing rows in non-clustered indexes and rebuilding an index. */
DBCC CHECKDB('DBName', REPAIR_ALLOW_DATA_LOSS); /* Tries to repair all reported errors. These repairs can cause some data loss. */

--Maybe need to rebuild the log file if lost.
ALTER DATABASE DBName REBUILD LOG ON (NAME = DBName_log, FILENAME = 'C:\Path\To\DBName_log.ldf');

--When the database is repaired, bring it back to Multi-User mode.
ALTER DATABASE DBName SET MULTI_USER;