question

CrisConner avatar image
CrisConner asked

The same index got corrupted twice.

I am having a problem with a non-clustered index getting corrupted. It happened once 2 weeks ago and we thought it was a problem with the SAN because we are a version behind on the firmware. We are planning on updating the firmware this weekend. Today the database suffered corruption from the same exact index. My question is, how does an index get corrupted twice in 2 weeks. It seems like if it were a hardware issue then the corruption would be random. The table is a heap with 3 non clustered indexes. One is a unique non-clustered index serving as the PK. The indexes were around 60% fragmented when this happened. Any ideas on what may be causing this or how to troubleshoot would be greatly appreciated. This is the output from checktable: 8951 Table error: table 'LoanDisposition' (ID 183512218). Data row does not have a matching index row in the index 'missing_index_177_176_LoanDisposition' (ID 55). Possible missing or invalid keys for the index row matching: 8955 Data row (1:1979829:0) identified by (HEAP RID = (1:1979829:0)) with index values 'LoanId = 'A5F4A13E-76B9-4ECD-82E1-64F2BE824230' and ASFGrade = '' and HEAP RID = (1:1979829:0)'. 8951 Table error: table 'LoanDisposition' (ID 183512218). Data row does not have a matching index row in the index 'missing_index_177_176_LoanDisposition' (ID 55). Possible missing or invalid keys for the index row matching: 8955 Data row (1:3187567:6) identified by (HEAP RID = (1:3187567:6)) with index values 'LoanId = 'E9FD845A-BFEE-4012-A803-9AFD10A02DDF' and ASFGrade = '' and HEAP RID = (1:3187567:6)'. 8951 Table error: table 'LoanDisposition' (ID 183512218). Data row does not have a matching index row in the index 'missing_index_177_176_LoanDisposition' (ID 55). Possible missing or invalid keys for the index row matching: 8955 Data row (1:4265343:10) identified by (HEAP RID = (1:4265343:10)) with index values 'LoanId = '74F1D4FA-CC43-40C7-B447-C6BA6801ADE1' and ASFGrade = '' and HEAP RID = (1:4265343:10)'. 8951 Table error: table 'LoanDisposition' (ID 183512218). Data row does not have a matching index row in the index 'missing_index_177_176_LoanDisposition' (ID 55). Possible missing or invalid keys for the index row matching: 8955 Data row (1:5460033:8) identified by (HEAP RID = (1:5460033:8)) with index values 'LoanId = 'C6F1C5A4-95A1-4636-BE9C-1D70CDF07E68' and ASFGrade = '' and HEAP RID = (1:5460033:8)'. 8951 Table error: table 'LoanDisposition' (ID 183512218). Data row does not have a matching index row in the index 'missing_index_177_176_LoanDisposition' (ID 55). Possible missing or invalid keys for the index row matching: 8955 Data row (1:5460033:6) identified by (HEAP RID = (1:5460033:6)) with index values 'LoanId = 'A3BB06F7-7A99-4322-BA9E-7E617BBCF03F' and ASFGrade = '' and HEAP RID = (1:5460033:6)'. 8952 Table error: table 'LoanDisposition' (ID 183512218). Index row in index 'missing_index_177_176_LoanDisposition' (ID 55) does not match any data row. Possible extra or invalid keys for: 8956 Index row (1:11878310:31) with values (LoanId = 'C6F1C5A4-95A1-4636-BE9C-1D70CDF07E68' and ASFGrade = 'D' and HEAP RID = (1:5460033:8)) pointing to the data row identified by (HEAP RID = (1:5460033:8)). 8952 Table error: table 'LoanDisposition' (ID 183512218). Index row in index 'missing_index_177_176_LoanDisposition' (ID 55) does not match any data row. Possible extra or invalid keys for: 8956 Index row (1:11879083:217) with values (LoanId = 'A5F4A13E-76B9-4ECD-82E1-64F2BE824230' and ASFGrade = 'D' and HEAP RID = (1:1979829:0)) pointing to the data row identified by (HEAP RID = (1:1979829:0)). 8952 Table error: table 'LoanDisposition' (ID 183512218). Index row in index 'missing_index_177_176_LoanDisposition' (ID 55) does not match any data row. Possible extra or invalid keys for: 8956 Index row (1:11879199:195) with values (LoanId = 'A3BB06F7-7A99-4322-BA9E-7E617BBCF03F' and ASFGrade = 'D' and HEAP RID = (1:5460033:6)) pointing to the data row identified by (HEAP RID = (1:5460033:6)). 8952 Table error: table 'LoanDisposition' (ID 183512218). Index row in index 'missing_index_177_176_LoanDisposition' (ID 55) does not match any data row. Possible extra or invalid keys for: 8956 Index row (1:11879331:129) with values (LoanId = 'E9FD845A-BFEE-4012-A803-9AFD10A02DDF' and ASFGrade = 'C' and HEAP RID = (1:3187567:6)) pointing to the data row identified by (HEAP RID = (1:3187567:6)). 8952 Table error: table 'LoanDisposition' (ID 183512218). Index row in index 'missing_index_177_176_LoanDisposition' (ID 55) does not match any data row. Possible extra or invalid keys for: 8956 Index row (1:11879529:95) with values (LoanId = '74F1D4FA-CC43-40C7-B447-C6BA6801ADE1' and ASFGrade = 'A' and HEAP RID = (1:4265343:10)) pointing to the data row identified by (HEAP RID = (1:4265343:10)). 8990 CHECKDB found 0 allocation errors and 10 consistency errors in table 'LoanDisposition' (object ID 183512218). 8989 CHECKDB found 0 allocation errors and 10 consistency errors in database ''.
indexcorruption
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

1 Answer

· Write an Answer
Grant Fritchey avatar image
Grant Fritchey answered
There's no real way to know for sure why any given corruption occurred because so many different things can cause it. But, repeated corruption is usually an indication of some type of hardware issue, the disks themselves, the disk controllers or the connections to the disks, I would focus there as the source of the problem. Be happy that it's just a clustered index that is corrupting because that is the single easiest recovery there is. But I would get someone in to thoroughly investigate the storage system, not just upgrading the firmware, but validating that everything is working through a thorough I/O check using whatever tools you have supplied by the SAN admins.
2 comments
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Hi Grant, thanks for taking the time to answer. Doesn’t seem odd though that it is the same index? Like lightning striking twice in the same place?
0 Likes 0 ·
Not really. A heap table with three non-clustered indexes, you're seeing some fragmentation. Storage is probably hitting a hardware snag, whatever it might be, and the snag in question has caused a corruption on the index, so it hits the same index again under no doubt similar circumstances. A giant pain, but not too shocking.
0 Likes 0 ·

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.