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