How to Recover SharePoint 2013 Databases from Suspect ModeJanuary 29th, 2013 | Author: Lydia Bronze | Filed under: CloudShare, Dev / Test, SharePoint | Tags: CloudShare, Database, How-To, server error, SharePoint, SharePoint 2013, SharePoint Configuration, SharePoint Server, SQL, SQL Server, SQL Server 2012
I restarted my SharePoint server, opened Central Administration and encountered the following error:
Server Error in ‘/’ Application
Description: An application error occurred on the server.
In order to troubleshoot this issue I had to check couple of thing:
- Make sure SQL Server services are up and running
- Make sure the IIS application pools are started
- Review Windows logs and gather more information about the server. I noticed the following event:
SQL Database ‘SharePoint_Config’ on SQL Server instance ‘C4968397007′ not found. Additional error information from SQL Server is included below.
Cannot open database “SharePoint_Config” requested by the login. The login failed. Login failed for user ‘DC07\SQLSvc’.
This event made me suspect something is wrong with my SQL Server. I opened SQL Server management studio and noticed that some of my most critical SharePoint databases are not accessible and set to suspect mode.
What is a suspect mode in SQL Server database?
Suspect mode might be caused by many reasons like unavailable or corrupted database files, hardware failure etc.
Don’t worry! This situation is reversible.
Here’s a quick guide of how to recover your SharePoint databases from suspect mode:
Open your SQL Server management studio and execute the following queries one after another:
- Run the following query. sp_resetstatus command will turn off suspect flag on the database.
EXEC sp_resetstatus ‘SharePoint_Config’;
After executing this query you’ll see the following warning. Don’t worry, this doesn’t mean you did something wrong.
- The next step is to set the database to an Emergency mode. This can be done by executing following query:
ALTER DATABASE SharePoint_Config SET EMERGENCY
After executing this query your database should look like this:
Once we set the database to an Emergency mode it temporarily becomes a Read Only database.
- Execute the following query in order to check the logical and physical integrity of the objects in the database.
- To complete the process, run the following queries:
SharePoint_Config SET SINGLE_USER
WITH ROLLBACK IMMEDIATE
DBCC CheckDB (‘SharePoint_Config’, REPAIR_ALLOW_DATA_LOSS)
ALTER DATABASE SharePoint_Config SET MULTI_USER
DBCC CheckDB (‘SharePoint_Config’)
Repeat this action for each one of the affected databases.
I ran some basic tests to make sure my SharePoint server is working properly again, looks like everything is back to track.