SharePoint Central Administration Content Database in Suspect Mode

What will be your first reaction when your Central Administration in SharePoint 2010 stops abruptly and its Content/Config database switches itself automatically to Suspect mode for no apparent reason, that too after a very stressful and super-hard working day.

I checked the ULS logs, Event Viewer logs, tried Application Pool recycles, Website Start/Stops, IIS Resets, even re-booted the server, even ran the SP 2010 Config Wizard but there was no luck. By now I started thinking that my SP 2010 farm is corrupt and needs to be re-configured/re-installed from scratch. A very painful thought in itself.

But as I further researched on this weird content database suspect issue, found this post on the forum which got me started: http://social.msdn.microsoft.com/forums/en-US/sqldisasterrecovery/thread/48cf82c9-2179-46f3-b009-11416a90d248/

However, I had to do a lot of R&D to get the actual SQL commands working.

Go to your SQL Server and ensure either your Central Admin Content Database or Config Database, if it is in Suspect mode, then this post is for you, see snapshot below:

So either a Central Admin Content database or a Config database can abruptly go into the suspect mode without any apparent logical reason. In my case, SharePoint Central Admin Content Database went into the Suspect mode.

To resolve the issue, follow steps in the following order as mentioned:

1. Go to your MSSQL\Data files that reside under: C:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\DATA


2. Identify your Central Admin Content Database name in my case it got screwed up and was in the suspect: mode: so this is my content database name SharePoint_AdminContent_38c5cc2d-aeec-4dc2-b7a5-65457250ae2c


NOTE: Please take a backup of the corrupted .mdf and .ldf files, before following other steps.

3. Open your SQL Server Management Studio ->, New Query and it opens up your SQL Query editor, copy and paste the query below and change the highlighted to your database name:

Use master

--Verify whether Database has any issues
EXEC sp_resetstatus "SharePoint_AdminContent_38c5cc2d-aeec-4dc2-b7a5-65457250ae2c.mdf"

---Alter database and put it on Emergency Mode
ALTER DATABASE "SharePoint_AdminContent_38c5cc2d-aeec-4dc2-b7a5-65457250ae2c" SET EMERGENCY
DBCC checkdb('SharePoint_AdminContent_38c5cc2d-aeec-4dc2-b7a5-65457250ae2c')

--Set the database in the Single User mode
ALTER DATABASE "SharePoint_AdminContent_38c5cc2d-aeec-4dc2-b7a5-65457250ae2c" SET SINGLE_USER WITH ROLLBACK IMMEDIATE

--Repair the database and allow data loss
DBCC CheckDB('SharePoint_AdminContent_38c5cc2d-aeec-4dc2-b7a5-65457250ae2c',REPAIR_ALLOW_DATA_LOSS)

--Set the database back to Multi-User mode
ALTER DATABASE "SharePoint_AdminContent_38c5cc2d-aeec-4dc2-b7a5-65457250ae2c" SET MULTI_USER

--Ensure Database is reset
EXEC sp_resetstatus 'SharePoint_AdminContent_38c5cc2d-aeec-4dc2-b7a5-65457250ae2c'

Execute all the commands in your SQL Query Editor and there you go, Go back to your SQL Management Studio and you can see that the (Suspect) mode issue against the Content/Config database is fixed and the database got fully repaired and restored.

To verify the same, hit your Central Administration and it starts working just fine...