Monday, November 19, 2012

Recover SQL Server Database from SUSPECT Mode

Recently working with one of my colleague, I noticed that the content database of a SharePoint web application on his VM started appearing with "Suspect" mode in SQL Server 2008 R2. As a result of this, dependent web application was not accessible.

This issue occurs when the primary filegroup is damaged and the database cannot be recovered during the startup of the SQL Server. This is the situation when SQL Database goes into "Suspect" mode.

In order to solve this issue, a user who is member of sysadmin fixed server role in SQL Server Database can perform below mentioned command steps:

Step 1 – Use the Master database
Use Master
Step 2 – Verify that database has issues
EXEC sp_resetstatus ‘Dbname

Step 3 – Put the database in emergency mode
SQL Server's emergency mode allows you to repair the database by repairing to last normal state.
 ALTER DATABASE Dbname SET EMERGENCY
DBCC checkdb(‘Dbname’)
 Step 4 Set the database in single user mode
ALTER DATABASE Dbname SET SINGLE_USER WITH ROLLBACK IMMEDIATE
 Step 5 – Repair the database with data loss
DBCC CheckDB (‘Dbname’, REPAIR_ALLOW_DATA_LOSS)
 
Step 6 – Set the database in multi-user mode
ALTER DATABASE Dbname SET MULTI_USER

Step 7 – Verify that database is reset
EXEC sp_resetstatus ‘Dbname
After running the script on the Master database, Dbname (WSS_Content in my case) database suspect mode was fixed and I was able to access the content web application.

No comments:

Post a Comment