Recovering SharePoint database from the Suspect Mode

Hi Guys,

In this post I am explaining how we recover a sharepoint content database from “suspect” mode.  . This is not a sharepoint specific issue, it’s the issue with the sql  server db. 

Possible reasons to go in to Suspect Mode

• Corruptions in the log/datafiles,
• Hardware failure,
• Antivirus programs ,  
• Low disk space on the server or
• Sudden shutdown of server while executing the transactions which were not rollback by server .etc.

Generally the db will be appear like this in suspect mode

Steps to Recover

Run the below commands in db 

EXEC sp_resetstatus 'SharePoint_Config' 

This command removes the suspect flag on the database 

Then set the db to Emergency mode, so that the db becomes read only and accessible to only privileged users like sysadmins 

ALTER DATABASE SharePoint_Config SET EMERGENCY 

Check the database for any inconsistency by running this following query 

DBCC CHECKDB ('SharePoint_Config') 

Then bring the database in to Single User mode by roll backing any transactions are present in the database 

ALTER DATABASE SharePoint_Config SET SINGLE_USER 

WITH ROLLBACK IMMEDIATE

Now take the backup of the database, since the next commands which we are going to execute are “One Way”  , there is no way to get the state back after execution. So for safety take a backup.

DBCC CheckDB (SharePoint_Config, REPAIR_ALLOW_DATA_LOSS) 

Now bring back the database to Multi User Mode

ALTER DATABASE SharePoint_Config SET MULTI_USER 

Refresh the db, now you should be able to see the db in usual mode . Now the users should be able to access the sharepoint site.

If you find any data loss, you can restore the database from the backup which we took in the above step.

You can cross check all the commands here

Hope this is helpful..!!

Thanks

Purna