“Content Database is in a Failed State” – Detailed Troubleshooting Guide

The “Content Database is in a failed state” error in SharePoint occurs when a content database is either corrupt, inaccessible, or in an offline state. This issue can lead to:

• Site collections becoming unavailable
• Web applications not loading properly
• Data loss risks if not addressed promptly

This guide provides a detailed step-by-step approach to diagnose and fix content database issues.

---

🔍 Step 1: Identify the Affected Content Database

Before taking any action, determine which content database is in a failed state.

✅ Using SharePoint Central Administration:

1. Open SharePoint Central Administration.
2. Go to Manage Content Databases:
   • Navigate to Application Management → Manage Content Databases.
3. Select the affected Web Application.
4. Look for any database listed as “Failed” or “Offline”.

✅ Using PowerShell to List Database Status:

Run the following command to check the status of all SharePoint content databases:

Get-SPContentDatabase | Select Name, Id, Status

If any database shows “Failed”, continue troubleshooting.

---

🔍 Step 2: Check the Database State in SQL Server

Sometimes, SharePoint reports a database failure due to SQL Server issues.

✅ Steps to Verify Database Status in SQL Server:

1. Log in to the SQL Server hosting SharePoint databases.
2. Open SQL Server Management Studio (SSMS).
3. Run the following query: SELECT name, state_desc FROM sys.databases;
4. Check the state_desc column for the affected content database.
   • Possible states:
     • ONLINE → Database is working fine.
     • OFFLINE → Database is disabled and needs to be brought online.
     • SUSPECT → Database is corrupted.
     • EMERGENCY → Database is in read-only mode for recovery.

---

🔍 Step 3: Bring the Content Database Online (If Offline)

If the database is in OFFLINE mode, bring it back online.

✅ SQL Query to Bring Database Online:

ALTER DATABASE [YourContentDB] SET ONLINE;

If successful, test the database in SharePoint again.

---

🔍 Step 4: Repair a Suspect Database

If the database is in SUSPECT mode, it means SQL Server detected corruption.

✅ Steps to Repair a Suspect Database:

1. Set the database to EMERGENCY mode: ALTER DATABASE [YourContentDB] SET EMERGENCY;
2. Run DBCC CHECKDB to check for corruption: DBCC CHECKDB([YourContentDB]) WITH NO_INFOMSGS, ALL_ERRORMSGS;
3. If errors are found, attempt an automatic repair: DBCC CHECKDB([YourContentDB], REPAIR_ALLOW_DATA_LOSS);
4. Set the database back to MULTI_USER mode: ALTER DATABASE [YourContentDB] SET MULTI_USER;

After repair, restart SharePoint services and test database connectivity.

---

🔍 Step 5: Reattach the Database in SharePoint

If the database was corrupt and repaired, you may need to detach and reattach it in SharePoint.

✅ Detach the Content Database via PowerShell:

Dismount-SPContentDatabase -Identity "YourContentDB"

Wait for confirmation before proceeding.

✅ Reattach the Content Database in SharePoint:

Mount-SPContentDatabase -Name "YourContentDB" -DatabaseServer "YourSQLServer" -WebApplication "http://YourWebAppURL"

This will force SharePoint to reestablish a connection with the database.

---

🔍 Step 6: Verify Database Permissions

Incorrect SQL Server permissions can cause a database to fail.

✅ Steps to Ensure Proper Permissions:

1. Open SQL Server Management Studio (SSMS).
2. Navigate to Security → Logins.
3. Find the SharePoint Farm Account (e.g., SPFarm).
4. Right-click and select Properties.
5. Ensure the account has the following server roles:
   • dbcreator
   • securityadmin
6. Under User Mapping, ensure the affected content database has:
   • db_owner role assigned.

If changes are made, restart IIS and SharePoint Timer Service.

---

🔍 Step 7: Check Event Viewer Logs for Errors

SharePoint and SQL Server logs can provide additional details.

✅ Steps to Check Logs in Event Viewer:

1. Open Run (Win + R) → Type eventvwr.msc.
2. Navigate to Windows Logs → Application.
3. Look for errors related to SQL Server, SharePoint, or IIS.
4. If specific error codes appear, investigate further.

---

🔍 Step 8: Restart SharePoint Services and IIS

After database repairs, restart services to refresh the connection.

✅ Restart IIS:

iisreset /noforce

✅ Restart SharePoint Timer Service:

Restart-Service SPTimerV4

✅ Restart SharePoint Administration Service:

Restart-Service SPAdminV4

---

🔍 Step 9: Remove Orphaned Databases (If Necessary)

If the database is still failing, it may be orphaned (linked to a non-existing web application).

✅ Use PowerShell to Find Orphaned Databases:

Get-SPDatabase | Where-Object { $_.Exists -eq $false }

If an orphaned database is found, remove it using:

Remove-SPContentDatabase -Identity "YourContentDB"

Reattach the database using Step 5.

---

🔍 Step 10: Restore the Database from a Backup (Last Resort)

If corruption cannot be fixed, restore from a backup.

✅ Steps to Restore Database in SQL Server:

1. Open SQL Server Management Studio (SSMS).
2. Right-click Databases → Restore Database.
3. Select From Device and choose the latest backup file.
4. Ensure the database name matches the original content database.
5. Click OK to start the restore process.

✅ Reattach the Restored Database in SharePoint:

Mount-SPContentDatabase -Name "YourRestoredDB" -DatabaseServer "YourSQLServer" -WebApplication "http://YourWebAppURL"

---