SQL Server Database SUSPECT state

03 October,2011 by Jack Vamvas

According to BOL , a database in SUSPECT state 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”

 There are numerous reasons for a SQL Server database to be marked SUSPECT.  A  suitable and tightly monitored SQL Server  database backup strategy is critical.

The suspect state requires that SQL started a rollback or crash recovery . The crash recovery or rollback failed  during the action

Some common causes

1. Missing device when SQL Server starts - this won't cause a SUSPECT state, but a RECOVERY_PENDING state. But ensuing actions may lead to a SUSPECT state

2. Denial of access to a database resource by the operating system

3. A drive where log files reside goes offline.See point 1

4. Anti-virus or 3rd party software blocking access

5.  Killing a DBCC comand  if there's corruption involved: Failure of rollback of big job and renaming db,  e.g  a developer  is performing cleanup of a  database realised that it brings your server on its knees, the developer stops a job which starts rollback and it takes forever. The stopping of the DBCC comand won't cause the SUSPECT , unless there's some corruption

6 . A  hardware/ network issue. Needs investigation and  can be difficult to find.

7. Corruption of database file(s)


Actions to take if a database in SUSPECT status:

1)                  Don’t reset status immediately, start a restore or any other change that is difficult to reverse.

2)                  Check Logs for relevant messages. Quite often they are unavailable, so consider some remedial action

3)                  Contact DBA and propose a) a server restart b) change to emergency mode where copies can be taken

4)                  If a suitable BACKUP is available enact a RESTORE procedure.

5)                  Test RESTORE procedure regularly to ensure integrity

Related Posts

SQL Server - Top 10 DBA mistakes

SQL Server - Decrease database risks with minimal spend

SQL Server - Recover from a Suspect database

SQL Database Status with sys.databases

Author: Jack Vamvas (http://www.sqlserver-dba.com)


Verify your Comment

Previewing your Comment

This is only a preview. Your comment has not yet been posted.

Your comment could not be posted. Error type:
Your comment has been posted. Post another comment

The letters and numbers you entered did not match the image. Please try again.

As a final step before posting your comment, enter the letters and numbers you see in the image below. This prevents automated programs from posting comments.

Having trouble reading this image? View an alternate.


Post a comment on SQL Server Database SUSPECT state

sqlserver-dba.com | SQL Server Performance Tuning | SQL Server DBA:Everything | FAQ | Contact|Copyright & Disclaimer