DBCC CHECKDB Job start failing Due to Consistency Error

Hi all finally I got the chance to work on DBCC CHECK DB maintenance job failure due to consistency error. Please  see below step how to resolve this issue if occur on your environment.

 When DBCC CHECKDB job got failed due to consistency error follows to below steps to resolve the issue:-

Error message Like :-

1.  CHECKDB found 1 allocation errors and 0 consistency errors in table 'table_Name’ (object ID 1043456720).
2.  Msg 8951, Level 16, State 1, Line 1

Table error: table 'Table_name' (ID 198734567). Data row does not have a matching index rowin the index 'Index_name_PK' (ID 3). Possible missing or invalid keys for the index row matching:

Msg 8955, Level 16, State 1, Line 1

Data row (1:507955:1) identified by (Component = 'SMS_AD_SECURITY_GROUP_DISCOVERY_AGENT' and Time = '2015-02-12 19:25:03.087' and UNIQUIFIER = 0) with index values 'RecordID = 112994885 and Component = 'SMS_AD_SECURITY_GROUP_DISCOVERY_AGENT' and Time = '2015-02-12 19:25:03.087' and UNIQUIFIER = 0'.

Msg 8951, Level 16, State 1, Line 1

Identify corrupted Objects:-

Through above error we can identify on which object id and index id has consistency issue against Index but sometime its complicated because error provide only object id so below script will help to find exact corrupted object and related to index .

Use the below script to find index name and exact object name with object_ID:-

   Select * from [sys.objects] where ]object_id]='106157654320'

   Go

   EXEC sp_HelpIndex 'Table_Name';

 Prerequisite

Prerequisite before apply any changes on object or corrupted index

First try to run DBCC CHECKTBLE against corrupted Table if still showing same error takes further steps to resolve this.

Raise an incident with concern person or team who belong to corrupted database and ask him to reliable time when we can rebuild this corrupted index to resolve consistency issue.

Once you get approval go ahead with below steps:-

Make sure you have current backup if not take full back up on the server with Copy_Only command.

Take object backup on which you are going to rebuild or recreate the index.

Solution:-

  First try to rebuild the corrupted index by below script or GUI

 ALTER INDEX Index_name ON Table_Name

 REBUILD;

Once rebuild complete try to run CHECKDB again if still issue not fix then Drop the index and recreate.

Do not drop index directly try to generate script DROP and create index by GUI and re-run this script this script will drop and create index from scratch.

Now run DBCC CHECKDB(DB_Name) and DBCC CHECKTABLE(Table_Name) against corrupted database and Table it should be complete without any consistency issue.