DB Suspect Recovery

Run the CHECKDB command with “REPAIR_ALLOW_DATA_LOSS” option.

DBCC CHECKDB (DatabaseName, REPAIR_ALLOW_DATA_LOSS) WITH NO_INFOMSGS, ALL_ERRORMSGS;

To get the exact reason of a database going into suspect mode can be found using the following query,

DBCC CHECKDB (‘YourDBname’) WITH NO_INFOMSGS, ALL_ERRORMSGS

EXEC sp_resetstatus 'DBName'

GO

ALTER DATABASE DBName SET EMERGENCY

DBCC checkdb('DBname')

ALTER DATABASE DBName SET SINGLE_USER WITH ROLLBACK IMMEDIATE

DBCC CheckDB ('DBName', REPAIR_ALLOW_DATA_LOSS)

ALTER DATABASE DBName SET MULTI_USER

GO