How to Recover SharePoint 2013 SUSPECT Databases

After several updates on SQL and SharePoint servers all night long, the next day, it is not possible to access the SharePoint Central Admin anymore...

Here is an old memo (April 2015) that I stored that can be useful for someone else.

The Issue

After a long night of updates on some SQL and SharePoint servers which resulted in a reboot on each of them; the next morning, an error message had replaced the SharePoint Central Administration:

To fix it, you can check:

• The SQL Server is correctly rebooted and operational
• The IIS Application Pools are "start"
• The Windows Events logs

In my case, a SharePoint Foundation (3760) critical error is logged:

SQL Database 'SharePoint_Config' on SQL Server instance 'dev' 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 'SPS\SPFarm'.

Thus, there is a problem of connexion to the database and more especially to the "SharePoint_Config" one, which is essential to the proper functioning of SharePoint.

It is necessary to check the database, thanks to SQL Server Management Studio, and note that several databases are switched in Suspect mode.

What is the Suspect mode into SQL Server?

At that point, the suspect databases cannot be used anymore; cannot be read nor write.

The Suspect mode can originate from several reasons like:

• Database files inaccessible or corrupted (frequently due to insufficient disk space)
• Hardware failure (unexpected physical reboot)
• SQL Server crash
• etc.

The Solution

Now that we know the cause, here are the steps to fix this issue:

1. Open SQL Server Management Studio
2. Open a new SQL Query page

3. Execute the following queries one after another:

-- Use the Master database
Use Master

-- Verify that database has issues
EXEC sp_resetstatus 'SharePoint_Config'

This query will disable the suspect database flag. Now:

ALTER DATABASE SharePoint_Config SET EMERGENCY

This query allows switching the database into EMERGENCY mode. You can note that the database becomes red with the (Emergency) suffix:

DBCC checkdb('SharePoint_Config')

This query allows checking the physical and logical integrity of objects in the database.
Finally, execute the latest following requests:

-- Set the database in single user mode
ALTER DATABASE SharePoint_Config SET SINGLE_USER WITH ROLLBACK IMMEDIATE

-- Repair the database with data loss
DBCC CheckDB ('SharePoint_Config', REPAIR_ALLOW_DATA_LOSS)

-- Set the database in multi-user mode
ALTER DATABASE SharePoint_Config SET MULTI_USER

-- Verify that database is reset
EXEC sp_resetstatus 'SharePoint_Config'

That's it 🙂

Hoping this post will help you 😉

• Tags:
• Infrastructure
• SharePoint 2013
• SQL Server