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
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)
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