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.
Common human reaction is to get scarred, pissed-off, drop the F-bombs on SharePoint 2010, drink lots of coffee, get irritated and shout on friends and family... :)
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. Sadly, none of them worked. 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...
NOTE: This post is true for any Content Database/Config Database in your SharePoint 2010 farm and not subject to only the Central administration web application. It will work equally well, if any of your current Web Application/Site Collection Content database gets corrupted and switches into the Suspect mode.
I hope it helps,
Common human reaction is to get scarred, pissed-off, drop the F-bombs on SharePoint 2010, drink lots of coffee, get irritated and shout on friends and family... :)
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. Sadly, none of them worked. 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...
NOTE: This post is true for any Content Database/Config Database in your SharePoint 2010 farm and not subject to only the Central administration web application. It will work equally well, if any of your current Web Application/Site Collection Content database gets corrupted and switches into the Suspect mode.
I hope it helps,
Hi Mehuil
ReplyDeleteYou just saved my life. All other articles on this subject talks about restoring the database but when you haven't got a backup and are low on time, this proved to be a much better solution. I have just performed the solution in my environment and it worked like charm. So a 50 foot statue of you will soon be places in the center of copenhagen to remind everyone how great you are... Or at least in my world you have just become my biggest idol. thanks again for posting this article.
Best regards
Søren Bjerre
I was struggling with this for over a week as well but this post made my life so easy... thanks man
ReplyDeletethanks man, you saved my day!
ReplyDeleteI have already faced this problem and I know how hard the situation goes. I was trying hard to get rid of this problem but took long to get out of it. This is definitely going to help people like me.
ReplyDeleteThanks a lot
ReplyDeleteoh my god - you saved me! thanks so much!
ReplyDeleteThanks Mehuil. you solved my problem.
ReplyDeletePriceless!!!
ReplyDeleteI ran into a very similar issue when the building the servers were hosted in suffered a catastrophic shutdown. This was a development system residing on two VMs where regular snapshot backups of the VMs were made. Once getting the computers back up and running, I noticed the SQL Server VM would not come up right. It had apparently suffered some corruption in the VMDK file or supporting files... So, we go back a week and try restoring an earlier image. The SQL Server has 22 databases on it - but SQL Server Service would not start. Doing a system database restore using the install media was necessary (after a few days of banging around and googling for answers). This at least put the database in a state where I could log in and open it up in SSMS. Imagine my disappointment in finding 11 out of the 22 databases were in SUSPECT mode. I found a number of articles that went through the steps you outlined. I performed them for each of the 11. But, wouldn't you know it, one critical database is not restorable - it reports 2 errors in the DBCC CHECKDB function - msg 5028 - The system could not activate enough of the database to rebuild the log. Apparently the mdf file is truncated by the operating system. I tried creating a blank new database, giving it the same name as the suspect - and then stopping SQL Services, deleting the new mdf and replacing it with the suspect and deleting the new log file. Restarting the SQL and going through the steps above still wouldn't give me any access.
DeleteUltimately, I completely rebuilt my development environment, chalking this experience down as a learning tool and let me know that I should not rely on VM Snapshots. I will rather use a different backup paradigm and experiment for better reliability. I cannot afford to have a paying client experience what I did and be told their data is lost - or at least that it will take days to rebuild the system. This experience took me two weeks from beginning to end and though I didn't lose my data - that is WAY TOO long for recovery.
Thank you SO much for this awesome article! I spent hours of frustration before finding this page. I followed these steps exactly and was able to recover the Central Admin database.
ReplyDelete