SQL Server 2016 – Interesting DBCC CHECKDB Scenario

dbccnonclustered-indexsql-server-2016

I woke up this morning to corruption in one of my databases. I have a job that runs daily DBCC CHECKDB's for all databases on a server. The output indicated corruption in only ONE table (fortunately).

Msg 8935, Level 16, State 1, Line 1
Table error: Object ID 1946163866, index ID 11, partition ID 72057601280442368, alloc unit ID 72057601393164288 (type In-row data). The previous link (5:216001) on page (5:216002) does not match the previous page (5:2603841) that the parent (5:3902159), slot 23 expects for this page.
Msg 8936, Level 16, State 1, Line 1
Table error: Object ID 1946163866, index ID 11, partition ID 72057601280442368, alloc unit ID 72057601393164288 (type In-row data). B-tree chain linkage mismatch. (5:628894)->next = (5:628895), but (5:628895)->Prev = (5:2602254).
Msg 8935, Level 16, State 1, Line 1
Table error: Object ID 1946163866, index ID 11, partition ID 72057601280442368, alloc unit ID 72057601393164288 (type In-row data). The previous link (5:1165706) on page (5:1165707) does not match the previous page (5:2602253) that the parent (5:1529), slot 33 expects for this page.
Msg 8980, Level 16, State 1, Line 1
Table error: Object ID 1946163866, index ID 11, partition ID 72057601280442368, alloc unit ID 72057601393164288 (type In-row data). Index node page (5:2239068), slot 33 refers to child page (5:2602254) and previous child (5:628894), but they were not encountered.
Msg 8980, Level 16, State 1, Line 1
Table error: Object ID 1946163866, index ID 11, partition ID 72057601280442368, alloc unit ID 72057601393164288 (type In-row data). Index node page (5:3902159), slot 22 refers to child page (5:2603841) and previous child (5:216001), but they were not encountered.
Msg 8981, Level 16, State 1, Line 1
Table error: Object ID 1946163866, index ID 10, partition ID 72057601280507904, alloc unit ID 72057601393229824 (type In-row data). The next pointer of (5:1212172) refers to page (5:3489726). Neither (5:3489726) nor its parent were encountered. Possible bad chain linkage.
Msg 8980, Level 16, State 1, Line 1
Table error: Object ID 1946163866, index ID 11, partition ID 72057601280442368, alloc unit ID 72057601393164288 (type In-row data). Index node page (5:1529), slot 32 refers to child page (5:2602253) and previous child (5:1165706), but they were not encountered.
Msg 8978, Level 16, State 1, Line 1
Table error: Object ID 1946163866, index ID 10, partition ID 72057601280507904, alloc unit ID 72057601393229824 (type In-row data). Page (5:3489727) is missing a reference from previous page (5:1212172). Possible chain linkage problem.
Msg 2533, Level 16, State 1, Line 1
Table error: page (5:2602253) allocated to object ID 1946163866, index ID 11, partition ID 72057601280442368, alloc unit ID 72057601393164288 (type In-row data) was not seen. The page may be invalid or may have an incorrect alloc unit ID in its header.
Msg 2533, Level 16, State 1, Line 1
Table error: page (5:2602254) allocated to object ID 1946163866, index ID 11, partition ID 72057601280442368, alloc unit ID 72057601393164288 (type In-row data) was not seen. The page may be invalid or may have an incorrect alloc unit ID in its header.
Msg 2533, Level 16, State 1, Line 1
Table error: page (5:2602255) allocated to object ID 1946163866, index ID 11, partition ID 72057601280442368, alloc unit ID 72057601393164288 (type In-row data) was not seen. The page may be invalid or may have an incorrect alloc unit ID in its header.
Msg 2533, Level 16, State 1, Line 1
Table error: page (5:2603840) allocated to object ID 1946163866, index ID 11, partition ID 72057601280442368, alloc unit ID 72057601393164288 (type In-row data) was not seen. The page may be invalid or may have an incorrect alloc unit ID in its header.
Msg 2533, Level 16, State 1, Line 1
Table error: page (5:2603841) allocated to object ID 1946163866, index ID 11, partition ID 72057601280442368, alloc unit ID 72057601393164288 (type In-row data) was not seen. The page may be invalid or may have an incorrect alloc unit ID in its header.
Msg 2533, Level 16, State 1, Line 1
Table error: page (5:2603842) allocated to object ID 1946163866, index ID 11, partition ID 72057601280442368, alloc unit ID 72057601393164288 (type In-row data) was not seen. The page may be invalid or may have an incorrect alloc unit ID in its header.
Msg 8935, Level 16, State 1, Line 1
Table error: Object ID 1946163866, index ID 11, partition ID 72057601280442368, alloc unit ID 72057601393164288 (type In-row data). The previous link (5:2602255) on page (5:15918) does not match the previous page (5:15917) that the parent (5:3904979), slot 8 expects for this page.
Msg 8936, Level 16, State 1, Line 1
Table error: Object ID 1946163866, index ID 11, partition ID 72057601280442368, alloc unit ID 72057601393164288 (type In-row data). B-tree chain linkage mismatch. (5:15917)->next = (5:15918), but (5:15918)->Prev = (5:2602255).
CHECKDB found 0 allocation errors and 16 consistency errors in table 'AperioCompare.PolicyAmendmentCompare' (object ID 1946163866).
CHECKDB found 0 allocation errors and 16 consistency errors in database 'Aperio'.
repair_allow_data_loss is the minimum repair level for the errors found by DBCC CHECKDB (Aperio).

I'm always looking for the last line so I can figure out the minimum level of recovery involved. In this case, it says "repair_allow_data_loss is the minimum repair level for the errors found by DBCC CHECKDB".

I looked at the Object ID and verified OBJECT_NAME(1946163866) indeed pointed to my ONE table. The error report showed problems with index ID's 10 and 11. I then selected from sys.indexes for object_id=1946163866 and found the index rows for indexes 10 and 11 – both of these indexes are non-clustered. I dropped and recreated both non-clustered indexes and re-ran my DBCC CHECKDB – no errors were reported.

I'm puzzled by the minimum level of recovery reported by the DBCC CHECKDB as being repair_allow_data_loss. Shouldn't this be rebuild?

Am I missing something obvious here?

Best Answer

Yup - there are some errors where CHECKDB picks REPAIR_ALLOW_DATA_LOSS even though it's a nonclustered index. You should be able to manually rebuild these (begin tran, disable, rebuild, commit tran). My guess is a lost write, stale read, or possibly a new bug in 2016.