How to resolve SQL Server 2005 Index Corrupt issue

techras SQL Server2005

Hi All,

I recently came across the following errors on trying to do an integrity check on one of my DBs

Msg 8951, Level 16, State 1, Line 1
Table error: table ‘LIVE.CBNHEA’ (ID 1652916960). Data row does not have a matching index row in the index ‘CBNHEA_ROWID’ (ID 2). Possible missing or invalid keys for the index row matching:
Msg 8955, Level 16, State 1, Line 1
Data row (1:3726817:1) identified by (HEAP RID = (1:3726817:1)) with index values ‘ROWID = 5679187 and HEAP RID = (1:3726817:1)’.
Msg 8951, Level 16, State 1, Line 1
Table error: table ‘LIVE.CBNHEA’ (ID 1652916960). Data row does not have a matching index row in the index ‘CBNHEA_CBH0’ (ID 3). Possible missing or invalid keys for the index row matching:
Msg 8955, Level 16, State 1, Line 1
Data row (1:3726817:1) identified by (HEAP RID = (1:3726817:1)) with index values ‘STOFCY_0 = ‘HLT’ and ITMREF_0 = ‘ and HEAP RID = (1:3726817:1)’.
Msg 8951, Level 16, State 1, Line 1
Table error: table ‘LIVE.CBNHEA’ (ID 1652916960). Data row does not have a matching index row in the index ‘CBNHEA_CBH1’ (ID 4). Possible missing or invalid keys for the index row matching:
Msg 8955, Level 16, State 1, Line 1
Data row (1:3726817:1) identified by (HEAP RID = (1:3726817:1)) with index values ‘STOFCY_0 = ‘HLT’ and LLC_0 = 99 and ITMREF_0 = ‘ and HEAP RID = (1:3726817:1)’.
CHECKDB found 0 allocation errors and 3 consistency errors in table ‘LIVE.CBNHEA’ (object ID 1652916960).
CHECKDB found 0 allocation errors and 3 consistency errors in database ‘xxxxx’.
repair_rebuild is the minimum repair level for the errors found by DBCC CHECKDB (xxxxx).

Then I ran the dbcc CHECKDB with ‘repair_rebuild’ on single user mode and it fixed the issue straight away.

This is what I did;

1. Put the DB on single user mode – In order to do this, right-click on the DB and select properties and then go to options, in the options select “Restrict Access” and then select “Single_User” and click “Yes”. You will have to close the management studio and reconnect again before proceeding to the next step.

2. After login on to management studio open up a query window that points to the relevant DB. Type the following

dbcc CHECKDB(‘dbname’,‘repair_rebuild’)

3. run the dbcc CHECKDB(‘dbname’)

This time all the inconsistency errors are gone 🙂

Happy Coding 🙂

7 thoughts on “How to resolve SQL Server 2005 Index Corrupt issue”

  4. wow that worked for 131 of 134 error messages.
    had to run it twice on our SQL Server 2005.
    it left these 3 errors, which were a lot different. any ideas on how fix these?
    your fan
    DBCC results for ‘TMNSPSRA’.
    Msg 8914, Level 16, State 1, Line 1
    Incorrect PFS free space information for page (1:9369) in object ID 2117582582, index ID 0, partition ID 138777892093952, alloc unit ID 138777892093952 (type In-row data). Expected value 100_PCT_FULL, actual value 95_PCT_FULL.
    Msg 8914, Level 16, State 1, Line 1
    Incorrect PFS free space information for page (1:213937) in object ID 2117582582, index ID 0, partition ID 138777892093952, alloc unit ID 138777892093952 (type In-row data). Expected value 100_PCT_FULL, actual value 95_PCT_FULL.
    Msg 8914, Level 16, State 1, Line 1
    Incorrect PFS free space information for page (1:302612) in object ID 2117582582, index ID 0, partition ID 138777892093952, alloc unit ID 138777892093952 (type In-row data). Expected value 95_PCT_FULL, actual value 100_PCT_FULL.
    There are 507709 rows in 5737 pages for object “TMNSPSRA”.
    CHECKDB found 0 allocation errors and 3 consistency errors in table ‘TMNSPSRA’ (object ID 2117582582)

    Reply

Leave a comment