Foros‎ > ‎SQL Server‎ > ‎

How To Repair SUSPECT Database

publicado a la‎(s)‎ 12 nov. 2013 6:27 por Ignacio Gonzalez
Sometimes we see a SharePoint error "Cannot connect to the configuration database". One of the reasons for this could be the Respective DataBase is set to SUSPECT mode.

Reasons for database SUSPECT state:
1. Database corruption.
2. Not enough space for the SQL Server to recover the database during startup.
3. Insufficient memory or disk space.
4. Unexpected SQL Server Shutdown, Power failure or a Hardware failure.

Follow the below steps to repair the SUSPECT database:
1. Identify all the databases which are in SUSPECT mode
  1. USE master  
  2. GO  
  3. SELECT NAME,STATE_DESC FROM SYS.DATABASES   
  4. WHERE STATE_DESC='SUSPECT'  
  5. GO  
Note:Check the latest log for the database which is marked as suspect. 
SQL Server -> Management -> SQL Server Logs

2. Get the database first into EMERGENCY mode to repair it
  1. USE master  
  2. GO   
  3. ALTER DATABASE "databasename" SET EMERGENCY  
  4. GO  
Note: The EMERGENCY database is one way which means read-only.

3. Repair the Database in emergency mode
  1. DBCC checkdb('databasename')  
  2. ALTER DATABASE "databasename" SET SINGLE_USER WITH ROLLBACK IMMEDIATE  
  3. DBCC CheckDB ('databasename', REPAIR_ALLOW_DATA_LOSS)  
  4. ALTER DATABASE "databasename" SET MULTI_USER  
Comments