How to Fix SQL Server Databases in Suspect or Recovery Pending Mode

Once in a while your SQL database may show up as “Suspect” or “Recovery Pending.” Either way the database will be inaccessible. depending on the database your SharePoint server might become totally useless. For example, the two Forefront Identity Management (FIM) services required for User Profile Synchronization (UPS) may not start, which may prevent your Central Administration site from loading in your browser. As a result you wont’ be able to manage your SharePoint server. Again, depending on the databases that were impacted, your SharePoint Site Collections may still be available to end users. I know this sounds strange but this is exactly what I ran into this weekend.

The environment I was working on consisted of SQL Server 2012 Enterprise and SharePoint Server 2013 Enterprise. Both were running on separate virtual machines in Hyper-V.

I was unable to get the two FIM services to start. I started to look around. Everything in IIS was fine. All the services on SQL Server were running. All, except the two FIM, services were running on the SharePoint server. After taking a close look at the Event Viewer Application logs I noticed that the SharePoint server was having trouble communicating with the SQL Server. It was having problem with locating the Central Administration database SharePoint_AdminContent_<GUID>. Essentially, the Farm account wasn’t able to login and open this database. Obviously, the first thing that will come to mind is that it must be a permission issue but I was able to access the database fine only hours ago and have not made any changes to the servers. Here’s the critical error that was logged under the source SharePoint Foundation with Event ID 3760.

In addition, I noticed the following Event ID errors. Microsoft Resource Management Service Event ID 0 error “Service cannot be started. System.Data.SqlClient.SqlException: Cannot generate SSPI context.”

Forefront Identity Management Event ID 3 error “.Net SqlClient Data Provider. System.Data.SqlClient.SqlException: Cannot generate SSPI context.”

Microsoft.ResourceManagement.ServiceHealthResource Event ID error 26 “The Forefront Identity Manager Service was not able to initialize a timer necessary for supporting the execution of workflows.”

A SharePoint Foundation Event ID error 5586 “Unknown SQL Exception 0 occurred. The target principal name is incorrect. Cannot generate SSPI context.”

There were lots of additional errors that were expected under the circumstances. I moved my focus from SharePoint Server to the SQL Server so I can figure out what was causing the problems with the Central Administration database. I started the SQL Server 2012 Management Studio and looked at the databases. I noticed that the Central Administration content database called SharePoint_AdminContent_<GUID> was in Suspect mode and the UPS_Sync database was in Recovery Pending mode, as shown below.

I have to admit, I feel comfortable working with SQL Server but I am definitely not a SQL expert. After doing some research to understand what the Suspect mode and Recovery Pending mode meant, I finally found a great solution that prevented me from going through some major hoops. I have to give credit to Mehuil Bhuva who wrote this article in 2011 on SharePointFix.com that was very helpful. He apparently went through a lot of trouble to figure out the exact SQL commands that fixed this problem. In my case I have the SharePoint _AdminContent database in Suspect mode. I wasn’t able to really do anything to this database, such as backing it up. Deleting the log file for the database would have been a really dumb thing to do when you are trying to fix a database and is a complete No-No. SQL experts in public forums warned that I should never detach a Suspect mode database or else I could be in a big trouble so I decided to fix it. Not that I have many choices.

Here’s what you can do to fix the SQL database that are in Suspect or Recovery Pending mode.

1. Start SQL Server Management Studio.
2. Expand the databases.
3. Use Control+N to start a new SQL query.
4. First fix the database in the Suspect mode by using the following commands one by one. Replace SuspectModeDB<GUID> with the actual name of your SharePoint Central Administration content database which starts with SharePoint_AdminContent followed by a Globally Unique ID (GUID).

   NOTE: If you are going to copy and paste the text from my article then watch out for the quotes. Copying and pasting from a Web page often gives you unexpected results. It is best to first copy the text to a Notepad and then copy from the Notepad into your SQL Server editor.Use [master]

   EXEC sp_resetstatus “SuspectModeDB<GUID>”

   ALTER DATABASE “SuspectModeDB<GUID>” SET EMERGENCY DBCC checkdb(‘SuspectModeDB<GUID>‘)

   ALTER DATABASE “SuspectModeDB<GUID>” SET SINGLE_USER WITH ROLLBACK IMMEDIATE

   DBCC CheckDB(‘SuspectModeDB<GUID>‘,REPAIR_ALLOW_DATA_LOSS)

   ALTER DATABASE “SuspectModeDB<GUID>” SET MULTI_USER

   EXEC sp_resetstatus ‘SuspectModeDB<GUID>‘

5. At this point your database should be repaired and accessible. Verify that it is in working condition by starting the Central Administration site.
6. Repeat the same steps for the database that is in Recovery Pending mode. In my case it was one of the UPS datbases (UPS_Sync to be exact). Again replace REcoveryPendingModeDB with the name of your own database. The UPS databases usually do not have GUIDs at the end of the database names, unless you used the Initial Farm Configuration Wizard to configure your farm, which is a bad idea and you should completely avoid it. As a best practice, you should configure your farm services manually.Use [master]

   EXEC sp_resetstatus “RecoveryPendingModeDB”

   ALTER DATABASE “RecoveryPendingModeDB” SET EMERGENCY DBCC checkdb(‘RecoveryPendingModeDB’)

   ALTER DATABASE “RecoveryPendingModeDB” SET SINGLE_USER WITH ROLLBACK IMMEDIATE

   DBCC CheckDB(‘RecoveryPendingModeDB‘,REPAIR_ALLOW_DATA_LOSS)

   ALTER DATABASE “RecoveryPendingModeDB” SET MULTI_USER

   EXEC sp_resetstatus ‘RecoveryPendingModeDB‘

7. The Recovery Pending database should now be repaired and if it’s one of of your UPS databases then you should be able to start the FIM services successfully.

Don’t forget to backup all your databases after verifying that your SQL Server and SharePoint Server are both functioning properly.

Copyright ©2014 Zubair Alexander. All rights reserved.