SharePoint Database Consistency Check and Repair
Another fine evening, SCOM reported an IO issue in SQL Server box where SharePoint DBs residing. At the same time, End-users started getting errors: “Cannot complete this action. Please Try again.”
Alert: Logical consistency error after performing I/O on page Source: MSSQLSERVER Path: DBserver.domain.org Last modified by: System Last modified time: 3/21/2012 7:20:55 PM Alert description: SQL Server detected a logical consistency-based I/O error: incorrect checksum (expected: 0x4c2c50d1; actual: 0x4c6650b5). It occurred during a read of page (1:2505989) in database ID 25 at offset 0x000004c7a0a000 in file 'S:\Microsoft SQL Server\MSSQL.1\MSSQL\Data\WSS_Content.mdf'. Additional messages in the SQL Server error log or system event log may provide more detail. This is a severe error condition that threatens database integrity and must be corrected immediately. Complete a full database consistency check (DBCC CHECKDB). This error can be caused by many factors; for more information, see SQL Server Books Online.
Try the soft repairs first:
stsadm -o databaserepair -url https://site-coll-url -databasename “Content_Database_Name”
PowerShell equivalent to repair a SharePoint database would be:
$database = Get-SPDatabase "Content_Database_Name" $database.Repair($false); $database.Update();
If its not successfully repaired, Check the Database’s consistency by disconnecting database from SharePoint:
stsadm -o Deletecontentdb -url <URL-of-my-Web-App> -databasename wss_content
Log into SQL Server, Get into SSMS. Run these commands:
ALTER DATABASE WSS_CONTENT SET SINGLE_USER
DBCC CHECKDB (‘WSS_CONTENT’) WITH NO_INFOMSGS
SQL Server told me to run DBCC CHECKDB with “Repair_Allow_Data_Loss”. O.K, Let me try this before using repair_allow_data_loss:
DBCC CHECKDB(‘WSS_Content’, REPAIR_REBUILD)
Nope, It didn’t Help, it returned the same error.
DBCC CHECKDB(‘WSS_Content’, REPAIR_ALLOW_DATA_LOSS)
luckily it fixed the issue.
Verified the database consistency by running
DBCC CHECKDB (‘WSS_Content’) WITH NO_INFOMSGS
again. This time, no issues. we are done.
Set the database mode to Multi & add it back to SharePoint:
ALTER DATABASE WSS_CONTENT SET MULTI_USER
Added the DB back to SharePoint:
stsadm -o addcontentdb -url <URL-of-my-Web-App> -databasename wss_content
Same kind of issue happened recently with PAGE error. Tried Repair, Allow data Loss, But none worked.
finally, I got to restore the database from backups and figured out the root cause: Server hardware failure 🙁
2 thoughts on “SharePoint Database Consistency Check and Repair”
DBCC CheckDB will show you which tables have issues and whether that can be fixed.
If you are not able to access db at all then you can restore the latest db backup too and try to see how SharePoint accepts that or you can try Sharepoint Recovery software to quickly fix the database corruption.
Seriously you are so lucky because you have recovered your whole database by using repair_allow_data_loss with DBCC CHECKDB. As I know that DBCC CHECKDB with repair_allow_data_loss tries to repair corrupt database with minimum data loss. You may loss some amount of data. Sharing this with you because I am not lucky as you, I had used third party sql restore tool to recover complete data.