Sunday, March 25, 2012

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."

SCOM Alert:
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 http://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:

USE WSS_CONTENT
GO

ALTER DATABASE WSS_CONTENT SET SINGLE_USER
GO

DBCC CHECKDB ('WSS_CONTENT') WITH NO_INFOMSGS

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.

Alright, tried:
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.
DBCC CHECKDB ('WSS_Content') WITH NO_INFOMSGS

finally, I got to restore the database from backups and figured out the root cause: Server hardware failure :-(



You might also like:
SharePoint Usage Reports
Usage reports, collaboration and audit for SharePoint.
Document SharePoint Farm
Automatically generate SharePoint documentation.
*Sponsored


Check out these SharePoint products:

2 comments :

  1. Hello Salaudeen,

    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.

    ReplyDelete
  2. 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.

    ReplyDelete

Please Login and comment to get your questions answered!

You might also like:

Related Posts Plugin for WordPress, Blogger...