HOW TO REPAIR SUSPECT DATABASE IN SQL SERVER 2005 AND HIGHER VERSIONS

Reasons for SQL server becoming a suspect

1. Unexpected SQL server shutdown
2. Database cannot be opened due inaccessible files or insufficient memory
3.  No enough space to recover the database during startup
4. Corrupt database
5. Database files being held by operating system

The database will appear with an exclamation mark and the word suspect in bracket  e.g. Test(Suspect)


RECOVERY 

• Run the following TSQL to view which database are marked as suspect

Select Name,state_desc FROM sys.databases

where state_desc = 'suspect'

• Check the SQL error log under the maintenance node to see the error log for the database marked as suspect
• When a database is in suspect mode it cannot be queried , you need to bring it back to emergency mode using the following TSQL so that the data can be queried 

Alter database Test set emergency

• Run the following TSQL to check both logical and physical integrity of all objects

DBCC CHECKDB (test)

• Bring the database to a single user mode so that it can be repaired

Alter database test set single_user with rollback immediate

• Now repair the database with the following TSQL and allow data loss which is minimal

DBCC CHECKDB (test,REPAIR_ALLOW_DATA_LOSS)

• Finally set the database back to Multi User

Alter database test Set  Multi_user

Hope the information helps SQL DBAs out there