Index corruption - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

Data Corruption Issues

Index corruption

Author

Topic

a_k_
Starting Member

23 Posts

Posted - 2005-06-27 : 13:04:17

We found the following error 4 times in our SQL Server 2000 Service Pack 4 (SP4) log:

Error: 644, Severity: 21, State: 3
Could not find the index entry for RID '163030314f564d30353034200' in index page (1:40729731), index ID 0, database 'MyDB'..

The last time it happened in the middle of the delete process, previous time during the reading process.
We run checkDB and result is 162 consistency errors in database 'MyDB'. The errors are like that:

Server: Msg 2511, Level 16, State 2, Line 1
Table error: Object ID 1141579105, Index ID 0. Keys out of order on page (1:40729731), slots 14 and 15.
Server: Msg 2512, Level 16, State 1, Line 1
Table error: Object ID 1141579105, Index ID 0. Duplicate keys on page (1:40729731) slot 28 and page (1:40729731) slot 29.
Server: Msg 2511, Level 16, State 1, Line 1
Table error: Object ID 1141579105, Index ID 2. Keys out of order on page (1:41115503), slots 428 and 429.
Server: Msg 2511, Level 16, State 1, Line 1
Table error: Object ID 1141579105, Index ID 2. Keys out of order on page (1:41117738), slots 131 and 132.

What we have to do? Will rebuilding indexes help or we have to do some additional steps? Can Dbcc dbrecover help?
What may be the reason for the problem?

Thank you for your help,
a_k_

paulrandal
Yak with Vast SQL Skills

899 Posts

Posted - 2005-06-27 : 13:16:16

quote:
Originally posted by a_k_

We found the following error 4 times in our SQL Server 2000 Service Pack 4 (SP4) log:

Error: 644, Severity: 21, State: 3
Could not find the index entry for RID '163030314f564d30353034200' in index page (1:40729731), index ID 0, database 'MyDB'..

The last time it happened in the middle of the delete process, previous time during the reading process.
We run checkDB and result is 162 consistency errors in database 'MyDB'. The errors are like that:

Server: Msg 2511, Level 16, State 2, Line 1
Table error: Object ID 1141579105, Index ID 0. Keys out of order on page (1:40729731), slots 14 and 15.
Server: Msg 2512, Level 16, State 1, Line 1
Table error: Object ID 1141579105, Index ID 0. Duplicate keys on page (1:40729731) slot 28 and page (1:40729731) slot 29.
Server: Msg 2511, Level 16, State 1, Line 1
Table error: Object ID 1141579105, Index ID 2. Keys out of order on page (1:41115503), slots 428 and 429.
Server: Msg 2511, Level 16, State 1, Line 1
Table error: Object ID 1141579105, Index ID 2. Keys out of order on page (1:41117738), slots 131 and 132.

What we have to do? Will rebuilding indexes help or we have to do some additional steps? Can Dbcc dbrecover help?
What may be the reason for the problem?

Thank you for your help,
a_k_

You're non-clustered indexes are corrupt in some way. Can you post the complete output from CHECKDB please? Are there any signs of hardware problems in the errorlog or windows event log?

Thanks

Paul Randal
Dev Lead, Microsoft SQL Server Storage Engine

a_k_
Starting Member

23 Posts

Posted - 2005-06-27 : 13:34:50

There are signs of hardware problems in the errorlog or windows event log.

The checkDB result is:
<<<<<<<<<<<<<<<<<
DBCC results for 'MyDB'.
DBCC results for 'sysobjects'.
There are 233 rows in 5 pages for object 'sysobjects'.
DBCC results for 'sysindexes'.
There are 126 rows in 7 pages for object 'sysindexes'.
DBCC results for 'syscolumns'.
There are 482 rows in 9 pages for object 'syscolumns'.
DBCC results for 'systypes'.
There are 26 rows in 1 pages for object 'systypes'.
DBCC results for 'syscomments'.
There are 239 rows in 16 pages for object 'syscomments'.
DBCC results for 'sysfiles1'.
There are 2 rows in 1 pages for object 'sysfiles1'.
DBCC results for 'syspermissions'.
There are 49 rows in 1 pages for object 'syspermissions'.
DBCC results for 'sysusers'.
There are 12 rows in 1 pages for object 'sysusers'.
DBCC results for 'sysproperties'.
There are 0 rows in 0 pages for object 'sysproperties'.
DBCC results for 'sysdepends'.
There are 310 rows in 1 pages for object 'sysdepends'.
DBCC results for 'sysreferences'.
There are 0 rows in 0 pages for object 'sysreferences'.
DBCC results for 'sysfulltextcatalogs'.
There are 0 rows in 0 pages for object 'sysfulltextcatalogs'.
DBCC results for 'sysfulltextnotify'.
There are 0 rows in 0 pages for object 'sysfulltextnotify'.
DBCC results for 'sysfilegroups'.
There are 1 rows in 1 pages for object 'sysfilegroups'.
DBCC results for 'abc0312M'.
There are 98288 rows in 487 pages for object 'abc0312M'.
DBCC results for 'abc9908m'.
There are 6324 rows in 33 pages for object 'abc9908m'.
DBCC results for 'abc0407M'.
There are 890234 rows in 4408 pages for object 'abc0407M'.
DBCC results for 'abc0408M'.
There are 55028 rows in 273 pages for object 'abc0408M'.
DBCC results for 'abc0402M'.
There are 462726 rows in 2291 pages for object 'abc0402M'.
DBCC results for 'abc0401M'.
There are 940284 rows in 4655 pages for object 'abc0401M'.
DBCC results for 'abc0409M'.
There are 27282 rows in 136 pages for object 'abc0409M'.
DBCC results for 'abc0410M'.
There are 1178408 rows in 5834 pages for object 'abc0410M'.
DBCC results for 'abc0411M'.
There are 60251 rows in 299 pages for object 'abc0411M'.
DBCC results for 'abc0301M'.
There are 691367 rows in 3423 pages for object 'abc0301M'.
DBCC results for 'abc0303M'.
There are 41973 rows in 208 pages for object 'abc0303M'.
DBCC results for 'abc0302M'.
There are 95871 rows in 475 pages for object 'abc0302M'.
DBCC results for 'abc0412M'.
There are 307812 rows in 1524 pages for object 'abc0412M'.
DBCC results for 'abc0304M'.
There are 1014005 rows in 5020 pages for object 'abc0304M'.
DBCC results for 'abc0501M'.
There are 869438 rows in 4305 pages for object 'abc0501M'.
DBCC results for 'abc0403M'.
There are 44226 rows in 219 pages for object 'abc0403M'.
DBCC results for 'abc0502M'.
There are 110765 rows in 549 pages for object 'abc0502M'.
DBCC results for 'abc0311M'.
There are 91338 rows in 453 pages for object 'abc0311M'.
DBCC results for 'abc0404M'.
There are 1138068 rows in 5634 pages for object 'abc0404M'.
DBCC results for 'abc0503M'.
There are 49464 rows in 245 pages for object 'abc0503M'.
Server: Msg 2511, Level 16, State 2, Line 1
Table error: Object ID 1141579105, Index ID 0. Keys out of order on page (1:40729731), slots 14 and 15.
Server: Msg 2512, Level 16, State 1, Line 1
Table error: Object ID 1141579105, Index ID 0. Duplicate keys on page (1:40729731) slot 28 and page (1:40729731) slot 29.
Server: Msg 2511, Level 16, State 1, Line 1
Table error: Object ID 1141579105, Index ID 2. Keys out of order on page (1:41115503), slots 428 and 429.
Server: Msg 2511, Level 16, State 1, Line 1
Table error: Object ID 1141579105, Index ID 2. Keys out of order on page (1:41117738), slots 131 and 132.
Server: Msg 2511, Level 16, State 1, Line 1
Table error: Object ID 1141579105, Index ID 2. Keys out of order on page (1:41118184), slots 513 and 514.
Server: Msg 2511, Level 16, State 1, Line 1
Table error: Object ID 1141579105, Index ID 2. Keys out of order on page (1:41121795), slots 326 and 327.
Server: Msg 2511, Level 16, State 1, Line 1
Table error: Object ID 1141579105, Index ID 2. Keys out of order on page (1:41124944), slots 525 and 526.
Server: Msg 2511, Level 16, State 1, Line 1
Table error: Object ID 1141579105, Index ID 2. Keys out of order on page (1:41124944), slots 526 and 527.
Server: Msg 2511, Level 16, State 1, Line 1
Table error: Object ID 1141579105, Index ID 2. Keys out of order on page (1:41132546), slots 189 and 190.
Server: Msg 2511, Level 16, State 1, Line 1
Table error: Object ID 1141579105, Index ID 2. Keys out of order on page (1:41134155), slots 211 and 212.
Server: Msg 2511, Level 16, State 1, Line 1
Table error: Object ID 1141579105, Index ID 2. Keys out of order on page (1:41137999), slots 585 and 586.
Server: Msg 2511, Level 16, State 1, Line 1
Table error: Object ID 1141579105, Index ID 2. Keys out of order on page (1:41139021), slots 110 and 111.
Server: Msg 2511, Level 16, State 1, Line 1
Table error: Object ID 1141579105, Index ID 2. Keys out of order on page (1:41142155), slots 468 and 469.
Server: Msg 2511, Level 16, State 1, Line 1
Table error: Object ID 1141579105, Index ID 2. Keys out of order on page (1:41146136), slots 103 and 104.
Server: Msg 2511, Level 16, State 1, Line 1
Table error: Object ID 1141579105, Index ID 2. Keys out of order on page (1:41147128), slots 25 and 26.
Server: Msg 2511, Level 16, State 1, Line 1
Table error: Object ID 1141579105, Index ID 2. Keys out of order on page (1:41149904), slots 529 and 530.
Server: Msg 2511, Level 16, State 1, Line 1
Table error: Object ID 1141579105, Index ID 2. Keys out of order on page (1:41150427), slots 282 and 283.
Server: Msg 2511, Level 16, State 1, Line 1
Table error: Object ID 1141579105, Index ID 16. Keys out of order on page (1:41293530), slots 63 and 64.
Server: Msg 2511, Level 16, State 1, Line 1
Table error: Object ID 1141579105, Index ID 16. Keys out of order on page (1:41298354), slots 24 and 25.
Server: Msg 2511, Level 16, State 1, Line 1
Table error: Object ID 1141579105, Index ID 16. Keys out of order on page (1:41310816), slots 79 and 80.
Server: Msg 2511, Level 16, State 1, Line 1
Table error: Object ID 1141579105, Index ID 16. Keys out of order on page (1:41322898), slots 121 and 122.
Server: Msg 2511, Level 16, State 1, Line 1
Table error: Object ID 1141579105, Index ID 16. Keys out of order on page (1:41326356), slots 23 and 24.
Server: Msg 2511, Level 16, State 1, Line 1
Table error: Object ID 1141579105, Index ID 16. Keys out of order on page (1:41342925), slots 79 and 80.
Server: Msg 2511, Level 16, State 1, Line 1
Table error: Object ID 1141579105, Index ID 16. Keys out of order on page (1:41343048), slots 44 and 45.
Server: Msg 2511, Level 16, State 1, Line 1
Table error: Object ID 1141579105, Index ID 16. Keys out of order on page (1:41353542), slots 62 and 63.
Server: Msg 2511, Level 16, State 1, Line 1
Table error: Object ID 1141579105, Index ID 16. Keys out of order on page (1:41366314), slots 0 and 1.
Server: Msg 2511, Level 16, State 1, Line 1
Table error: Object ID 1141579105, Index ID 16. Keys out of order on page (1:41375940), slots 191 and 192.
Server: Msg 2511, Level 16, State 1, Line 1
Table error: Object ID 1141579105, Index ID 16. Keys out of order on page (1:41376196), slots 92 and 93.
Server: Msg 2511, Level 16, State 1, Line 1
Table error: Object ID 1141579105, Index ID 16. Keys out of order on page (1:41382503), slots 73 and 74.
Server: Msg 2511, Level 16, State 1, Line 1
Table error: Object ID 1141579105, Index ID 16. Keys out of order on page (1:42021758), slots 29 and 30.
Server: Msg 2511, Level 16, State 1, Line 1
Table error: Object ID 1141579105, Index ID 16. Keys out of order on page (1:42147865), slots 65 and 66.
Server: Msg 2511, Level 16, State 1, Line 1
Table error: Object ID 1141579105, Index ID 17. Keys out of order on page (1:41196352), slots 78 and 79.
Server: Msg 2511, Level 16, State 1, Line 1
Table error: Object ID 1141579105, Index ID 17. Keys out of order on page (1:41201950), slots 90 and 91.
DBCC results for 'Qtr'.
There are 39 rows in 1 pages for object 'Qtr'.
DBCC results for 'abcP'.
Server: Msg 2511, Level 16, State 1, Line 1
Table error: Object ID 1141579105, Index ID 17. Keys out of order on page (1:41223885), slots 189 and 190.
Server: Msg 2511, Level 16, State 1, Line 1
Table error: Object ID 1141579105, Index ID 17. Keys out of order on page (1:41224545), slots 46 and 47.
Server: Msg 2511, Level 16, State 1, Line 1
Table error: Object ID 1141579105, Index ID 17. Keys out of order on page (1:41237903), slots 193 and 194.
Server: Msg 2511, Level 16, State 1, Line 1
Table error: Object ID 1141579105, Index ID 17. Keys out of order on page (1:41240371), slots 76 and 77.
Server: Msg 2511, Level 16, State 1, Line 1
Table error: Object ID 1141579105, Index ID 17. Keys out of order on page (1:41245020), slots 175 and 176.
Server: Msg 2511, Level 16, State 1, Line 1
Table error: Object ID 1141579105, Index ID 17. Keys out of order on page (1:41253919), slots 115 and 116.
Server: Msg 2511, Level 16, State 1, Line 1
Table error: Object ID 1141579105, Index ID 17. Keys out of order on page (1:41274749), slots 24 and 25.
Server: Msg 2511, Level 16, State 1, Line 1
Table error: Object ID 1141579105, Index ID 17. Keys out of order on page (1:41399493), slots 206 and 207.
Server: Msg 2511, Level 16, State 1, Line 1
Table error: Object ID 1141579105, Index ID 17. Keys out of order on page (1:41407225), slots 163 and 164.
Server: Msg 2511, Level 16, State 1, Line 1
Table error: Object ID 1141579105, Index ID 17. Keys out of order on page (1:41408885), slots 19 and 20.
Server: Msg 2511, Level 16, State 1, Line 1
Table error: Object ID 1141579105, Index ID 17. Keys out of order on page (1:41529856), slots 8 and 9.
Server: Msg 2511, Level 16, State 1, Line 1
Table error: Object ID 1141579105, Index ID 17. Keys out of order on page (1:41983712), slots 55 and 56.
Server: Msg 2511, Level 16, State 1, Line 1
Table error: Object ID 1141579105, Index ID 18. Keys out of order on page (1:41102184), slots 74 and 75.
Server: Msg 2511, Level 16, State 1, Line 1
Table error: Object ID 1141579105, Index ID 18. Keys out of order on page (1:41102185), slots 255 and 256.
Server: Msg 2511, Level 16, State 1, Line 1
Table error: Object ID 1141579105, Index ID 18. Keys out of order on page (1:41113601), slots 209 and 210.
Server: Msg 2511, Level 16, State 1, Line 1
Table error: Object ID 1141579105, Index ID 18. Keys out of order on page (1:41155288), slots 278 and 279.
Server: Msg 2511, Level 16, State 1, Line 1
Table error: Object ID 1141579105, Index ID 18. Keys out of order on page (1:41160185), slots 192 and 193.
Server: Msg 2511, Level 16, State 1, Line 1
Table error: Object ID 1141579105, Index ID 18. Keys out of order on page (1:41165470), slots 296 and 297.
Server: Msg 2511, Level 16, State 1, Line 1
Table error: Object ID 1141579105, Index ID 18. Keys out of order on page (1:41173477), slots 299 and 300.
Server: Msg 2511, Level 16, State 1, Line 1
Table error: Object ID 1141579105, Index ID 18. Keys out of order on page (1:41181396), slots 140 and 141.
Server: Msg 2511, Level 16, State 1, Line 1
Table error: Object ID 1141579105, Index ID 18. Keys out of order on page (1:41194803), slots 125 and 126.
Server: Msg 2511, Level 16, State 1, Line 1
Table error: Object ID 1141579105, Index ID 18. Keys out of order on page (1:41194967), slots 121 and 122.
Server: Msg 2511, Level 16, State 1, Line 1
Table error: Object ID 1141579105, Index ID 18. Keys out of order on page (1:41195031), slots 25 and 26.
Server: Msg 2511, Level 16, State 1, Line 1
Table error: Object ID 1141579105, Index ID 18. Keys out of order on page (1:41556246), slots 76 and 77.
Server: Msg 2511, Level 16, State 1, Line 1
Table error: Object ID 1141579105, Index ID 18. Keys out of order on page (1:41985358), slots 93 and 94.
Server: Msg 2511, Level 16, State 1, Line 1
Table error: Object ID 1141579105, Index ID 18. Keys out of order on page (1:42018263), slots 112 and 113.
Server: Msg 2511, Level 16, State 1, Line 1
Table error: Object ID 1141579105, Index ID 19. Keys out of order on page (1:41461712), slots 185 and 186.
Server: Msg 2511, Level 16, State 1, Line 1
Table error: Object ID 1141579105, Index ID 19. Keys out of order on page (1:41461712), slots 187 and 188.
Server: Msg 2511, Level 16, State 1, Line 1
Table error: Object ID 1141579105, Index ID 19. Keys out of order on page (1:41465115), slots 215 and 216.
Server: Msg 2511, Level 16, State 1, Line 1
Table error: Object ID 1141579105, Index ID 19. Keys out of order on page (1:41469779), slots 288 and 289.
Server: Msg 2511, Level 16, State 1, Line 1
Table error: Object ID 1141579105, Index ID 19. Keys out of order on page (1:41469779), slots 290 and 291.
Server: Msg 2511, Level 16, State 1, Line 1
Table error: Object ID 1141579105, Index ID 19. Keys out of order on page (1:41469779), slots 291 and 292.
Server: Msg 2511, Level 16, State 1, Line 1
Table error: Object ID 1141579105, Index ID 19. Keys out of order on page (1:41487455), slots 392 and 393.
Server: Msg 2511, Level 16, State 1, Line 1
Table error: Object ID 1141579105, Index ID 19. Keys out of order on page (1:41487455), slots 394 and 395.
Server: Msg 2511, Level 16, State 1, Line 1
Table error: Object ID 1141579105, Index ID 19. Keys out of order on page (1:41493215), slots 125 and 126.
Server: Msg 2511, Level 16, State 1, Line 1
Table error: Object ID 1141579105, Index ID 19. Keys out of order on page (1:41493215), slots 127 and 128.
Server: Msg 2511, Level 16, State 1, Line 1
Table error: Object ID 1141579105, Index ID 19. Keys out of order on page (1:41498756), slots 231 and 232.
Server: Msg 2511, Level 16, State 1, Line 1
Table error: Object ID 1141579105, Index ID 19. Keys out of order on page (1:41505387), slots 291 and 292.
Server: Msg 2511, Level 16, State 1, Line 1
Table error: Object ID 1141579105, Index ID 19. Keys out of order on page (1:41505387), slots 293 and 294.
Server: Msg 2511, Level 16, State 1, Line 1
Table error: Object ID 1141579105, Index ID 19. Keys out of order on page (1:41505387), slots 295 and 296.
Server: Msg 2511, Level 16, State 1, Line 1
Table error: Object ID 1141579105, Index ID 19. Keys out of order on page (1:41511098), slots 282 and 283.
Server: Msg 2511, Level 16, State 1, Line 1
Table error: Object ID 1141579105, Index ID 20. Keys out of order on page (1:41588331), slots 75 and 76.
Server: Msg 2511, Level 16, State 1, Line 1
Table error: Object ID 1141579105, Index ID 20. Keys out of order on page (1:41591814), slots 191 and 192.
Server: Msg 2511, Level 16, State 1, Line 1
Table error: Object ID 1141579105, Index ID 20. Keys out of order on page (1:41592526), slots 58 and 59.
Server: Msg 2511, Level 16, State 1, Line 1
Table error: Object ID 1141579105, Index ID 20. Keys out of order on page (1:41598432), slots 34 and 35.
Server: Msg 2511, Level 16, State 1, Line 1
Table error: Object ID 1141579105, Index ID 20. Keys out of order on page (1:41598432), slots 36 and 37.
Server: Msg 2511, Level 16, State 1, Line 1
Table error: Object ID 1141579105, Index ID 20. Keys out of order on page (1:41598432), slots 37 and 38.
Server: Msg 2511, Level 16, State 1, Line 1
Table error: Object ID 1141579105, Index ID 20. Keys out of order on page (1:41617282), slots 340 and 341.
Server: Msg 2511, Level 16, State 1, Line 1
Table error: Object ID 1141579105, Index ID 20. Keys out of order on page (1:41618276), slots 317 and 318.
Server: Msg 2511, Level 16, State 1, Line 1
Table error: Object ID 1141579105, Index ID 20. Keys out of order on page (1:41624779), slots 177 and 178.
Server: Msg 2511, Level 16, State 1, Line 1
Table error: Object ID 1141579105, Index ID 20. Keys out of order on page (1:41624779), slots 179 and 180.
Server: Msg 2511, Level 16, State 1, Line 1
Table error: Object ID 1141579105, Index ID 20. Keys out of order on page (1:41630935), slots 334 and 335.
Server: Msg 2511, Level 16, State 1, Line 1
Table error: Object ID 1141579105, Index ID 20. Keys out of order on page (1:41638103), slots 6 and 7.
Server: Msg 2511, Level 16, State 1, Line 1
Table error: Object ID 1141579105, Index ID 20. Keys out of order on page (1:41638103), slots 8 and 9.
Server: Msg 2511, Level 16, State 1, Line 1
Table error: Object ID 1141579105, Index ID 20. Keys out of order on page (1:41638103), slots 10 and 11.
Server: Msg 2511, Level 16, State 1, Line 1
Table error: Object ID 1141579105, Index ID 20. Keys out of order on page (1:41644393), slots 110 and 111.
Server: Msg 2511, Level 16, State 1, Line 1
Table error: Object ID 1141579105, Index ID 21. Keys out of order on page (1:41721024), slots 55 and 56.
Server: Msg 2511, Level 16, State 1, Line 1
Table error: Object ID 1141579105, Index ID 21. Keys out of order on page (1:41721024), slots 57 and 58.
Server: Msg 2511, Level 16, State 1, Line 1
Table error: Object ID 1141579105, Index ID 21. Keys out of order on page (1:41725268), slots 173 and 174.
Server: Msg 2511, Level 16, State 1, Line 1
Table error: Object ID 1141579105, Index ID 21. Keys out of order on page (1:41731160), slots 259 and 260.
Server: Msg 2511, Level 16, State 1, Line 1
Table error: Object ID 1141579105, Index ID 21. Keys out of order on page (1:41731160), slots 261 and 262.
Server: Msg 2511, Level 16, State 1, Line 1
Table error: Object ID 1141579105, Index ID 21. Keys out of order on page (1:41731160), slots 262 and 263.
Server: Msg 2511, Level 16, State 1, Line 1
Table error: Object ID 1141579105, Index ID 21. Keys out of order on page (1:41750117), slots 300 and 301.
Server: Msg 2511, Level 16, State 1, Line 1
Table error: Object ID 1141579105, Index ID 21. Keys out of order on page (1:41751028), slots 7 and 8.
Server: Msg 2511, Level 16, State 1, Line 1
Table error: Object ID 1141579105, Index ID 21. Keys out of order on page (1:41757500), slots 119 and 120.
Server: Msg 2511, Level 16, State 1, Line 1
Table error: Object ID 1141579105, Index ID 21. Keys out of order on page (1:41757500), slots 121 and 122.
Server: Msg 2511, Level 16, State 1, Line 1
Table error: Object ID 1141579105, Index ID 21. Keys out of order on page (1:41763706), slots 3 and 4.
Server: Msg 2511, Level 16, State 1, Line 1
Table error: Object ID 1141579105, Index ID 21. Keys out of order on page (1:41770792), slots 62 and 63.
Server: Msg 2511, Level 16, State 1, Line 1
Table error: Object ID 1141579105, Index ID 21. Keys out of order on page (1:41770792), slots 64 and 65.
Server: Msg 2511, Level 16, State 1, Line 1
Table error: Object ID 1141579105, Index ID 21. Keys out of order on page (1:41770792), slots 66 and 67.
Server: Msg 2511, Level 16, State 1, Line 1
Table error: Object ID 1141579105, Index ID 21. Keys out of order on page (1:41777012), slots 116 and 117.
Server: Msg 2511, Level 16, State 1, Line 1
Table error: Object ID 1141579105, Index ID 22. Keys out of order on page (1:41854905), slots 108 and 109.
Server: Msg 2511, Level 16, State 1, Line 1
Table error: Object ID 1141579105, Index ID 22. Keys out of order on page (1:41856528), slots 167 and 168.
Server: Msg 2511, Level 16, State 1, Line 1
Table error: Object ID 1141579105, Index ID 22. Keys out of order on page (1:41857382), slots 259 and 260.
Server: Msg 2511, Level 16, State 1, Line 1
Table error: Object ID 1141579105, Index ID 22. Keys out of order on page (1:41863088), slots 133 and 134.
Server: Msg 2511, Level 16, State 1, Line 1
Table error: Object ID 1141579105, Index ID 22. Keys out of order on page (1:41868230), slots 359 and 360.
Server: Msg 2511, Level 16, State 1, Line 1
Table error: Object ID 1141579105, Index ID 22. Keys out of order on page (1:41868230), slots 360 and 361.
Server: Msg 2511, Level 16, State 1, Line 1
Table error: Object ID 1141579105, Index ID 22. Keys out of order on page (1:41880491), slots 290 and 291.
Server: Msg 2511, Level 16, State 1, Line 1
Table error: Object ID 1141579105, Index ID 22. Keys out of order on page (1:41883133), slots 197 and 198.
Server: Msg 2511, Level 16, State 1, Line 1
Table error: Object ID 1141579105, Index ID 22. Keys out of order on page (1:41889363), slots 127 and 128.
Server: Msg 2511, Level 16, State 1, Line 1
Table error: Object ID 1141579105, Index ID 22. Keys out of order on page (1:41890977), slots 345 and 346.
Server: Msg 2511, Level 16, State 1, Line 1
Table error: Object ID 1141579105, Index ID 22. Keys out of order on page (1:41896015), slots 347 and 348.
Server: Msg 2511, Level 16, State 1, Line 1
Table error: Object ID 1141579105, Index ID 22. Keys out of order on page (1:41902476), slots 76 and 77.
Server: Msg 2511, Level 16, State 1, Line 1
Table error: Object ID 1141579105, Index ID 22. Keys out of order on page (1:41904709), slots 254 and 255.
Server: Msg 2511, Level 16, State 1, Line 1
Table error: Object ID 1141579105, Index ID 22. Keys out of order on page (1:41908591), slots 381 and 382.
Server: Msg 2511, Level 16, State 1, Line 1
Table error: Object ID 1141579105, Index ID 22. Keys out of order on page (1:41909426), slots 65 and 66.
Server: Msg 2511, Level 16, State 1, Line 1
Table error: Object ID 1141579105, Index ID 23. Keys out of order on page (1:41848219), slots 358 and 359.
Server: Msg 2511, Level 16, State 1, Line 1
Table error: Object ID 1141579105, Index ID 23. Keys out of order on page (1:41848219), slots 360 and 361.
Server: Msg 2511, Level 16, State 1, Line 1
Table error: Object ID 1141579105, Index ID 23. Keys out of order on page (1:41848219), slots 362 and 363.
Server: Msg 2511, Level 16, State 1, Line 1
Table error: Object ID 1141579105, Index ID 23. Keys out of order on page (1:41848219), slots 364 and 365.
Server: Msg 2511, Level 16, State 1, Line 1
Table error: Object ID 1141579105, Index ID 23. Keys out of order on page (1:41848219), slots 366 and 367.
Server: Msg 2511, Level 16, State 1, Line 1
Table error: Object ID 1141579105, Index ID 23. Keys out of order on page (1:41848219), slots 368 and 369.
Server: Msg 2511, Level 16, State 1, Line 1
Table error: Object ID 1141579105, Index ID 23. Keys out of order on page (1:41848219), slots 370 and 371.
Server: Msg 2511, Level 16, State 1, Line 1
Table error: Object ID 1141579105, Index ID 23. Keys out of order on page (1:41848219), slots 372 and 373.
Server: Msg 2511, Level 16, State 1, Line 1
Table error: Object ID 1141579105, Index ID 23. Keys out of order on page (1:41848219), slots 374 and 375.
Server: Msg 2511, Level 16, State 1, Line 1
Table error: Object ID 1141579105, Index ID 23. Keys out of order on page (1:41848219), slots 376 and 377.
Server: Msg 2511, Level 16, State 1, Line 1
Table error: Object ID 1141579105, Index ID 23. Keys out of order on page (1:41848219), slots 378 and 379.
Server: Msg 2511, Level 16, State 1, Line 1
Table error: Object ID 1141579105, Index ID 23. Keys out of order on page (1:41848219), slots 380 and 381.
Server: Msg 2511, Level 16, State 1, Line 1
Table error: Object ID 1141579105, Index ID 23. Keys out of order on page (1:41848219), slots 382 and 383.
Server: Msg 2511, Level 16, State 1, Line 1
Table error: Object ID 1141579105, Index ID 23. Keys out of order on page (1:41916750), slots 419 and 420.
Server: Msg 2511, Level 16, State 1, Line 1
Table error: Object ID 1141579105, Index ID 23. Keys out of order on page (1:41916750), slots 420 and 421.
Server: Msg 2511, Level 16, State 1, Line 1
Table error: Object ID 1141579105, Index ID 24. Keys out of order on page (1:41694947), slots 134 and 135.
Server: Msg 2511, Level 16, State 1, Line 1
Table error: Object ID 1141579105, Index ID 24. Keys out of order on page (1:41695970), slots 212 and 213.
Server: Msg 2511, Level 16, State 1, Line 1
Table error: Object ID 1141579105, Index ID 24. Keys out of order on page (1:41695970), slots 214 and 215.
Server: Msg 2511, Level 16, State 1, Line 1
Table error: Object ID 1141579105, Index ID 24. Keys out of order on page (1:41695970), slots 216 and 217.
Server: Msg 8934, Level 16, State 1, Line 1
Table error: Object ID 1141579105, index ID 24. The high key value on page (1:41787587) (level 0) is not less than the low key value in the parent (1:41787986), slot 202 of the next page (1:41787588).
Server: Msg 2511, Level 16, State 1, Line 1
Table error: Object ID 1141579105, Index ID 24. Keys out of order on page (1:41787587), slots 446 and 447.
Server: Msg 2511, Level 16, State 1, Line 1
Table error: Object ID 1141579105, Index ID 24. Keys out of order on page (1:41799424), slots 350 and 351.
Server: Msg 2511, Level 16, State 1, Line 1
Table error: Object ID 1141579105, Index ID 24. Keys out of order on page (1:41799424), slots 352 and 353.
Server: Msg 2511, Level 16, State 1, Line 1
Table error: Object ID 1141579105, Index ID 24. Keys out of order on page (1:41799424), slots 354 and 355.
Server: Msg 2511, Level 16, State 1, Line 1
Table error: Object ID 1141579105, Index ID 24. Keys out of order on page (1:41799424), slots 356 and 357.
Server: Msg 2511, Level 16, State 1, Line 1
Table error: Object ID 1141579105, Index ID 24. Keys out of order on page (1:41808653), slots 414 and 415.
Server: Msg 2511, Level 16, State 1, Line 1
Table error: Object ID 1141579105, Index ID 24. Keys out of order on page (1:41808653), slots 416 and 417.
Server: Msg 2511, Level 16, State 1, Line 1
Table error: Object ID 1141579105, Index ID 24. Keys out of order on page (1:41808653), slots 418 and 419.
Server: Msg 2511, Level 16, State 1, Line 1
Table error: Object ID 1141579105, Index ID 24. Keys out of order on page (1:41808653), slots 420 and 421.
Server: Msg 2511, Level 16, State 1, Line 1
Table error: Object ID 1141579105, Index ID 25. Keys out of order on page (1:41580396), slots 344 and 345.
Server: Msg 2511, Level 16, State 1, Line 1
Table error: Object ID 1141579105, Index ID 25. Keys out of order on page (1:41651444), slots 401 and 402.
Server: Msg 2511, Level 16, State 1, Line 1
Table error: Object ID 1141579105, Index ID 25. Keys out of order on page (1:41654745), slots 312 and 313.
Server: Msg 2511, Level 16, State 1, Line 1
Table error: Object ID 1141579105, Index ID 25. Keys out of order on page (1:41654812), slots 23 and 24.
Server: Msg 2511, Level 16, State 1, Line 1
Table error: Object ID 1141579105, Index ID 25. Keys out of order on page (1:41659313), slots 401 and 402.
Server: Msg 2511, Level 16, State 1, Line 1
Table error: Object ID 1141579105, Index ID 25. Keys out of order on page (1:41659523), slots 184 and 185.
Server: Msg 2511, Level 16, State 1, Line 1
Table error: Object ID 1141579105, Index ID 25. Keys out of order on page (1:41659779), slots 274 and 275.
Server: Msg 2511, Level 16, State 1, Line 1
Table error: Object ID 1141579105, Index ID 25. Keys out of order on page (1:41660444), slots 383 and 384.
Server: Msg 2511, Level 16, State 1, Line 1
Table error: Object ID 1141579105, Index ID 25. Keys out of order on page (1:41661313), slots 67 and 68.
Server: Msg 2511, Level 16, State 1, Line 1
Table error: Object ID 1141579105, Index ID 25. Keys out of order on page (1:41667727), slots 202 and 203.
Server: Msg 2511, Level 16, State 1, Line 1
Table error: Object ID 1141579105, Index ID 25. Keys out of order on page (1:41679224), slots 49 and 50.
Server: Msg 2511, Level 16, State 1, Line 1
Table error: Object ID 1141579105, Index ID 25. Keys out of order on page (1:41682473), slots 106 and 107.
Server: Msg 2511, Level 16, State 1, Line 1
Table error: Object ID 1141579105, Index ID 25. Keys out of order on page (1:41688498), slots 281 and 282.
Server: Msg 2511, Level 16, State 1, Line 1
Table error: Object ID 1141579105, Index ID 25. Keys out of order on page (1:41691444), slots 124 and 125.
There are 12130250 rows in 209548 pages for object 'abcP'.
CHECKDB found 0 allocation errors and 162 consistency errors in table 'abcP' (object ID 1141579105).
DBCC results for 'abc0504M'.
There are 1251494 rows in 6196 pages for object 'abc0504M'.
DBCC results for 'abc0405M'.
There are 61522 rows in 305 pages for object 'abc0405M'.
DBCC results for 'abc0505M'.
There are 69125 rows in 343 pages for object 'abc0505M'.
DBCC results for 'abc0406M'.
There are 315501 rows in 1562 pages for object 'abc0406M'.
DBCC results for 'abc0506M'.
There are 20900 rows in 104 pages for object 'abc0506M'.
DBCC results for 'abc0305M'.
There are 72399 rows in 359 pages for object 'abc0305M'.
DBCC results for 'abc0306M'.
There are 37363 rows in 185 pages for object 'abc0306M'.
DBCC results for 'abc0307M'.
There are 981093 rows in 4857 pages for object 'abc0307M'.
DBCC results for 'c_abc0312P'.
There are 988522 rows in 16476 pages for object 'c_abc0312P'.
DBCC results for 'abc0308M'.
There are 187735 rows in 930 pages for object 'abc0308M'.
DBCC results for 'abc0309M'.
There are 34336 rows in 170 pages for object 'abc0309M'.
DBCC results for 'abc0310M'.
There are 1001954 rows in 4961 pages for object 'abc0310M'.
DBCC results for 'dtproperties'.
There are 0 rows in 0 pages for object 'dtproperties'.
CHECKDB found 0 allocation errors and 162 consistency errors in database 'MyDB'.
repair_allow_data_loss is the minimum repair level for the errors found by DBCC CHECKDB (MyDB ).
DBCC execution completed. If DBCC printed error messages, contact your system administrator.
>>>>>>>>>>>>>>>>>

Thank you very much for your help,
a_k_

paulrandal
Yak with Vast SQL Skills

899 Posts

Posted - 2005-06-27 : 13:37:22

quote:
Originally posted by a_k_

There are signs of hardware problems in the errorlog or windows event log.

What are the signs you mention above?

And what build number are you running?

This sounds like a hardware issue but I'd like to check to see if there are any known bugs you may be running into as well.

Thanks

Paul Randal
Dev Lead, Microsoft SQL Server Storage Engine

a_k_
Starting Member

23 Posts

Posted - 2005-06-27 : 13:51:21

I am sorry for the wrong expression: there is NO signs of the hardware problems that I can see. We are using RAID system for the hard drives and it seems to be healthy. The OS is Windows 2000 Server. No error messages (except those 644 messages) found in the log files.

The Build number is 2195 (from Enterprise Manager).

Thank you very much,
a_k_

paulrandal
Yak with Vast SQL Skills

899 Posts

Posted - 2005-06-27 : 13:58:06

quote:
Originally posted by a_k_

I am sorry for the wrong expression: there is NO signs of the hardware problems that I can see. We are using RAID system for the hard drives and it seems to be healthy. The OS is Windows 2000 Server. No error messages (except those 644 messages) found in the log files.

The Build number is 2195 (from Enterprise Manager).

Thank you very much,
a_k_

ok - what's the DB collation and the schema of the table/index that's corrupt?

Paul Randal
Dev Lead, Microsoft SQL Server Storage Engine

a_k_
Starting Member

23 Posts

Posted - 2005-06-27 : 14:33:22

The collation is default (no special settings).
The abcP table structure is:
Keyid char(6) NOT NULL DEFAULT '',
fld1 char(6) NOT NULL DEFAULT '',
LastName char(25) NOT NULL DEFAULT '',
FirstName char(20) NOT NULL DEFAULT '',
fld2 char(11) NOT NULL DEFAULT '',
fld3 char(6) NOT NULL DEFAULT '',
Sstartdat DATETIME NOT NULL DEFAULT 0,
senddat DATETIME NOT NULL DEFAULT 0,
proddat DATETIME NOT NULL DEFAULT 0,
fld4 char(2) NOT NULL DEFAULT '',
fld5 decimal(5) NOT NULL DEFAULT 0,
Stype char(1) NOT NULL DEFAULT '',
Activdet char(1) NOT NULL DEFAULT '',
CDKey char(4) NOT NULL DEFAULT '',
Enddoc char(6) NOT NULL DEFAULT '',
wonum char(6) NOT NULL DEFAULT '',
CONSTRAINT abcP_key1 PRIMARY KEY (keyid, cdkey)
INDEX abcPIDX_1 ON abcP(fld1)
INDEX abcPIDX_2 ON abcP(LastName)
INDEX abcPIDX_3 ON abcP(FirstName)
INDEX abcPIDX_4 ON abcP(fld2)
INDEX abcPIDX_5 ON abcP(fld3)
INDEX abcPIDX_6 ON abcP(Sstartdat)
INDEX abcPIDX_7 ON abcP(senddat)
INDEX abcPIDX_8 ON abcP(proddat)
INDEX abcPIDX_9 ON abcP(fld4)
INDEX abcPIDX_10 ON abcP(fld5)

Thank you,
a_k_

paulrandal
Yak with Vast SQL Skills

899 Posts

Posted - 2005-06-27 : 14:40:34

quote:
Originally posted by a_k_

The collation is default (no special settings).
The abcP table structure is:
Keyid char(6) NOT NULL DEFAULT '',
fld1 char(6) NOT NULL DEFAULT '',
LastName char(25) NOT NULL DEFAULT '',
FirstName char(20) NOT NULL DEFAULT '',
fld2 char(11) NOT NULL DEFAULT '',
fld3 char(6) NOT NULL DEFAULT '',
Sstartdat DATETIME NOT NULL DEFAULT 0,
senddat DATETIME NOT NULL DEFAULT 0,
proddat DATETIME NOT NULL DEFAULT 0,
fld4 char(2) NOT NULL DEFAULT '',
fld5 decimal(5) NOT NULL DEFAULT 0,
Stype char(1) NOT NULL DEFAULT '',
Activdet char(1) NOT NULL DEFAULT '',
CDKey char(4) NOT NULL DEFAULT '',
Enddoc char(6) NOT NULL DEFAULT '',
wonum char(6) NOT NULL DEFAULT '',
CONSTRAINT PACTP_key1 PRIMARY KEY (keyid, cdkey)
INDEX PACTPIDX_1 ON PACTP(fld1)
INDEX PACTPIDX_2 ON PACTP(LastName)
INDEX PACTPIDX_3 ON PACTP(FirstName)
INDEX PACTPIDX_4 ON PACTP(fld2)
INDEX PACTPIDX_5 ON PACTP(fld3)
INDEX PACTPIDX_6 ON PACTP(Sstartdat)
INDEX PACTPIDX_7 ON PACTP(senddat)
INDEX PACTPIDX_8 ON PACTP(proddat)
INDEX PACTPIDX_9 ON PACTP(fld4)
INDEX PACTPIDX_10 ON PACTP(fld4)

Thank you,
a_k_

You need to contact Product Support (http://support.microsoft.com). I suspect you've hit an issue to do with sort order of columns with trailing padding, although the only time we've seen it was with a cyrillic collation. If you ask them to contact me, I can provide them with the internal bug number.

Thanks

Thanks

Paul Randal
Dev Lead, Microsoft SQL Server Storage Engine

a_k_
Starting Member

23 Posts

Posted - 2005-06-27 : 14:55:49

If I need to fix this database now, can I just rebuild indexes?
Is the problem only in the indexes or the contents of the table may also be corrupted?
Does Microsoft have any fix for this problem? How they will help us?

Thank you very much for your help,
a_k_

paulrandal
Yak with Vast SQL Skills

899 Posts

Posted - 2005-06-27 : 15:03:58

quote:
Originally posted by a_k_

If I need to fix this database now, can I just rebuild indexes?
Is the problem only in the indexes or the contents of the table may also be corrupted?
Does Microsoft have any fix for this problem? How they will help us?

Thank you very much for your help,
a_k_

We've only seen this once during SP4 Beta testing and there's been no investigation/fix yet as there was a workaround in that case.

Try the following:
drop all indexes
create all non-clustered indexes
create the clustered index

Thanks

Paul Randal
Dev Lead, Microsoft SQL Server Storage Engine

Thrasymachus
Constraint Violating Yak Guru

483 Posts

Posted - 2005-06-27 : 15:18:19

Paul,
I hate to be a hassle, but I remember reading somewhere if you are going to drop and recreate your indices you should do it in this order:

1. drop nonclustered.
2. drop clustered
3. create clustered
4. create non clustered

It had something to do with the clustered index being used in the creation of the nonclustered index. So if you created the nonclustered first, the nonclustered would be rebuilt again when you created a clustered index causing additional overhead.

Is there something particular to this guy's problem that dictated the order of your workaround directions, is what I read nonsense or am I being an overly obsessive weirdo and there was no reasoning for the order of the last 2 steps you gave?

BTW, I really appreciate you hanging around here and DBForums.

Sean Roussy

Please backup all of your databases including master, msdb and model on a regular basis. I am tired of telling people they are screwed. The job you save may be your own.

I am available for consulting work. Just email me though the forum.

paulrandal
Yak with Vast SQL Skills

899 Posts

Posted - 2005-06-27 : 15:49:53

quote:
Originally posted by Thrasymachus

Paul,
I hate to be a hassle, but I remember reading somewhere if you are going to drop and recreate your indices you should do it in this order:

1. drop nonclustered.
2. drop clustered
3. create clustered
4. create non clustered

It had something to do with the clustered index being used in the creation of the nonclustered index. So if you created the nonclustered first, the nonclustered would be rebuilt again when you created a clustered index causing additional overhead.

Is there something particular to this guy's problem that dictated the order of your workaround directions, is what I read nonsense or am I being an overly obsessive weirdo and there was no reasoning for the order of the last 2 steps you gave?

BTW, I really appreciate you hanging around here and DBForums.

Sean Roussy

Please backup all of your databases including master, msdb and model on a regular basis. I am tired of telling people they are screwed. The job you save may be your own.

I am available for consulting work. Just email me though the forum.

You're just being a weirdo

You're absolutely right - but in this case, its specific to the problem I think he's hit. It may not work as the other instance included nvarchar columns, which he doesn't have in all the indexes.

It would also be interesting to see whether the clustered index keys are the same across the non-clustered indexes at the points they're corrupted.

Thanks

Paul Randal
Dev Lead, Microsoft SQL Server Storage Engine

a_k_
Starting Member

23 Posts

Posted - 2005-06-28 : 17:05:38

I dropped non-clustered indexes with commands:

DROP INDEX abcp.abcPIDX_1
DROP INDEX abcp.abcPIDX_2
DROP INDEX abcp.abcPIDX_3
DROP INDEX abcp.abcPIDX_4
DROP INDEX abcp.abcPIDX_5
DROP INDEX abcp.abcPIDX_6
DROP INDEX abcp.abcPIDX_7
DROP INDEX abcp.abcPIDX_8
DROP INDEX abcp.abcPIDX_9
DROP INDEX abcp.abcPIDX_10
DROP INDEX abcp.abcPIDX_11

Then I dropped clustered index with command:

ALTER TABLE abcp DROP CONSTRAINT abcP_key1

Then I created non-clustered indexes with commands:

CREATE INDEX abcPIDX_1 ON abcP(fld1)
CREATE INDEX abcPIDX_2 ON abcP(LastName)
CREATE INDEX abcPIDX_3 ON abcP(FirstName)
CREATE INDEX abcPIDX_4 ON abcP(fld2)
CREATE INDEX abcPIDX_5 ON abcP(fld3)
CREATE INDEX abcPIDX_6 ON abcP(Sstartdat)
CREATE INDEX abcPIDX_7 ON abcP(senddat)
CREATE INDEX abcPIDX_8 ON abcP(proddat)
CREATE INDEX abcPIDX_9 ON abcP(fld4)
CREATE INDEX abcPIDX_10 ON abcP(fld5)
CREATE INDEX abcPIDX_11 ON abcP(cdkey)

Then I tried to create the clustered index (primary key):

ALTER TABLE abcp ADD CONSTRAINT abcP_key1 PRIMARY KEY (keyid, cdkey)

and received error message that it cannot be created because duplicate entries found. We always had primary key on this table, so no duplicates could possibly be created. How it happened?

I checked the table for duplicates with the following query:

select keyid,cdkey,count(keyid)
from pactp
group by cdkey,keyid
having count(keyid) > 1

and found 14 sets of duplicate keyid + cdkey, 1 of them appear 3 times, other 2 times. In all the sets keyid have only 2 values.

I deleted non-clustered keyis and run this query again, the result is 14 rows with different cdkey value in some of the cases.

When I try to select the rows with duplicate keys - all the values are the same.

What happen to the table? Does it really contain duplicate entries? How they created when primary key exists? How we can fix it?

Thank you very much for your help,
a_k_

paulrandal
Yak with Vast SQL Skills

899 Posts

Posted - 2005-06-28 : 17:10:36

So it sounds like you've hit some sort of corruption or a bug. You need to call Product Support - this isn't something we can figure out through a forum.

Thanks

Paul Randal
Dev Lead, Microsoft SQL Server Storage Engine

rhyams80
Starting Member

2 Posts

Posted - 2006-07-27 : 13:52:05

Did you ever find a resolution? I am experiencing the exact same problem on a production server. The strangest things is the exact same sql install, sp, and db code run on test server without problem.

paulrandal
Yak with Vast SQL Skills

899 Posts

Posted - 2006-07-27 : 15:00:35

Are the test and prod boxes setup *exactly* the same way (e.g. maxdop, ANSI settings)? And are they *exactly* the same hardware?

Paul Randal
Lead Program Manager, Microsoft SQL Server Storage Engine + SQL Express
(Legalese: This posting is provided "AS IS" with no warranties, and confers no rights.)

rhyams80
Starting Member

2 Posts

Posted - 2006-07-27 : 15:17:50

Both of the machines are running windows 2000 server service pack 4 with sql server 2000 service pack 4. They have the same level of hot fixes as well. They machines are not the exact same model number. They do have the same ANSI settings.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources