Recover Sharepoint 2013 databases from suspect mode.

This Post courtesy to: Lydia Bronze
Original Post from: Blogs.cloudshare.com


I restarted my SharePoint server, opened Central Administration and encountered the following error:
Server Error in ‘/’ Application
Runtime Error
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.

DBCC checkdb(‘SharePoint_Config’)

• To complete the process, run the following queries:

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


January 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