SharePoint and Suspect Databases

SharePoint databases are a special breed and require specific actions to clear the “Suspect” label found in SQL Studio Manager.

The most common cause of a suspect databases is a disk issue. Run a full disk check with the repair option (“/R/ F /X”) on the drives that store the the data (MDF) and logs (LOG/TRN) files.

What not to do

1. Open SQL Server Management Studio
2. Run the following commands in order on the suspect database:

Many websites tell you to perform these steps. DO NOT DO THIS.

Replace your database name for each <insert databases name>

EXEC sp_resetstatus '<insert databases name>';
ALTER DATABASE <insert databases name> SET EMERGENCY
DBCC checkdb(‘<insert databases name>’)
ALTER DATABASE <insert databases name> SET SINGLE_USER WITH ROLLBACK IMMEDIATE
DBCC CheckDB (‘<insert databases name>’, REPAIR_ALLOW_DATA_LOSS)
ALTER DATABASE <insert databases name> SET MULTI_USER

To Test run this command again
DBCC CheckDB (‘<insert databases name>’)

Microsoft explains why in this article but the TL:DR says the option “REPAIR_ALLOW_DATA_LOSS” is a hatchet job and corrupts the databases even further plus Microsoft will no longer support your SharePoint installation.

What to do

1. Check the disk for errors
2. Restore from backup the suspect database

Checking the disk

Check the disk structure with the repair option for both the virtual disk and the underlying physical.

chkdsk <insert the drive letter>: /R /F /X

The switches tell CHKDSK to perform an offline check, fix issues with the file structure, and find/recover bad sectors.

After checking the disk

Special Case for Search Crawl Database

The search crawl database cannot be restored from backup but its contents are easily recreated. At the very worst you can delete the old search crawl database and add a new one.

Try the first method and then the second one.

Method 1: Wipe the crawl data

1. Login to Central Admin (CA) site
2. Goto your SharePoint Search Admin (SSA) site
3. Clear all crawl data
4. On the SQL server, open the SQL Management tool
5. Shrink the search crawl database files
6. Go back to the SSA site
7. Start a full crawl.
8. When the crawl is done test it. If it didn’t work move on to Method 2.

Method 2: Delete and recreate the Crawl DB

Using the Central Admin (CA) site

You cannot change the topology via the Central Admin site.  You have to use PowerShell.

Using PowerShell

1. Stop the search service
2. Open a SharePoint admin mode PowerShell console.
3. Edit and run the script below
4. Start the search service
5. Login to your Central Admin (CA) site
6. Go to your Search Service Administration site
7. Under Crawling, go to Crawl Sources
8. For each source start a full crawl
9. Test search when the crawl finishes

Script for PowerShell

$ServiceNameSSA = Get-SPEnterpriseSearchServiceApplication
$OldCrawlDB = "<insert the old crawl DB name>"
$NewCrawlDB = "<insert the new crawl DB name>"
Remove-SPEnterpriseSearchCrawlDatabase -Identity $OldCrawlDB -SearchApplication $ServiceName
New-SPEnterpriseSearchCrawlDatabase -SearchApplication $ServiceNameSSA-DatabaseName $NewCrawlDB

Special Case for Usage and Health logging Databases

The Usage and Health Service Application (UHSA) database cannot be restored from backup. The data in the database is constantly updating and the default is to it purged 14 days after its collected.

The only thing you can do is delete and recreate the database. Use any of the below methods.

Method 1: Via Central Admin site

Method 2: Using PowerShell Easy Config

This leaves the existing service application as is but changes the database it uses.

1. Login to the SharePoint server that hosts the UHSA.
2. Copy and edit the script to you server
3. Open a SharePoint PowerShell console with admin rights
4. Run the script
5. Login to the SQL server
6. Delete the old suspect database

The script

$ServiceAppName = Get-SPUsageApplication
$ServiceDBName = "<insert your new name here>"
Set-SPUsageApplication -Identity $ServiceAppName -DatabaseName $ServiceDBName 

The most common cause for this method to fail is the service application isn’t running.  This cause a blank name.

Helpful Tip: Finding the UH SA name

1. Open a SharePoint PowerShell console with admin rights
2. Run this command

Get-SPUsageApplication | Select DisplayName

If the command returns nothing then the UHSA isn’t running.

Method 3: Using PowerShell Full Config

If you want to control every aspect of configuring the UHSA.  You can also use this to setup the UHSA if it isn’t installed yet.

Configuration Variables
$UsageSAName = "Usage and Health Data Collection Service"
$DatabaseName = "WSS_Logging"
$DatabaseServer= "<insert SQL server's name>"

Get the Usage Service Application Service Instance
$ServiceInstance = Get-SPUsageService

Create new Usage Service application
New-SPUsageApplication -Name $UsageSAName -DatabaseServer $DatabaseServer -DatabaseName $DatabaseName -UsageService $ServiceInstance

Create Service Application Proxy
$proxy = Get-SPServiceApplicationProxy | where {$_.TypeName -eq "Usage and Health Data Collection Proxy"}
$proxy.Provision()