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..!!