Lydia Bronze

How to Recover SharePoint 2013 Databases from Suspect Mode

January 29th, 2013 | Author: | Filed under: CloudShare, Dev / Test, SharePoint | Tags: , , , , , , , , , ,


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.

01 - SharePoint 2013 Server Error in Application

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

02.1 - Windows logs

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. 02 - SharePoint 2013 databases are in 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.

04 - SQL Server reset status of database warning

  • 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:

06 - SharePoint_config database is set to emergency mode

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.

09 - Everything is back to track

I ran some basic tests to make sure my SharePoint server is working properly again, looks like everything is back to track.

 

About the author:
Lydia Bronze ( @LydiBee ) , Cloud Content Architect at CloudShare, is an experienced technologist with a diverse and deep background in infrastructure technologies, information security and technical training. Lydia's proven ability to architect, implement and provide solutions for advanced infrastructures using Microsoft and Open Source technologies makes her an invaluable asset to the team. She brings technical experience from a variety of industries, including the Defense and Finance sectors. In addition, Lydia has extensive SharePoint experience and has implemented corporate environments as well as custom SharePoint solutions for organizations of all sizes. Lydia is continuously seeking new challenges that will allow her to utilize her technical abilities.