SQL Server Database in Suspect Mode!!!!

Hey Everyone,

What if you suddenly get login failiures to a database and when u check it says the database is in SUSPECT mode??

As per msdn, suspect mode is 

At least the primary filegroup is suspect and may be damaged. The database cannot be recovered during startup of SQL Server. The database is unavailable. Additional action by the user is required to resolve the problem.

If you have a huge database which demands high up time, DBCC Checkdb to identify the root cause may not be the best solution. DBCC CheckDB can be time consuming.

--to check the status of checkdb

select dateadd(second,estimated_completion_time/1000, getdate()) as est_completion_time
,percent_complete,blocking_session_id,* from
sys.dm_exec_requests where session_id=sessionID_goes_here
or blocking_session_id=sessionID_goes_here

--to identify any memory dumps created
SELECT top 2 *
FROM sys.dm_server_memory_dumps
ORDER BY creation_time DESC

If you have a smaller database you could follow below steps to recovery your database.

1. EXEC sp_resetstatus 'DBName'
2. ALTER DATABASE DBName SET EMERGENCY
3. DBCC checkdb('DBName')
4. If you get errors from checkdb=> ALTER DATABASE DBName SET SINGLE_USER WITH ROLLBACK IMMEDIATE
5. DBCC CheckDB ('DBName ', REPAIR_ALLOW_DATA_LOSS)
6. ALTER DATABASE [DBName]  SET MULTI_USER

However, If your database is terabytes in size and unable to run dbcc checkdb, you could use dbcc checktable option on the suspicious tables to isolate any corruption.But the drawback is in EMERGANCY mode you are unable to execute DBCC Checktable ('tableName', REPAIR_ALLOW_DATA_LOSS).

We came across a simmilar situation recently.


Point in time recovery was useless since the corruption comes with the log backups, you need to identify the point before the corruption and restore until then to solve the issue which is not practical and can cause huge data loss.


Our approach which was suggested by microsoft was to rebuild the log file at the cost of losing data in the transaction log.

Simply you have to detach the database and attach it without the logfile. A new log file will be created and mapped to the database.

This way you can get the database online.

Remember to run a DBCC CHECKDB followed by the log rebuild and repair any inconsistancies.

Cheers