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
 Corruption in Clustered Index and more

Author  Topic 

ferule
Starting Member

7 Posts
Posted - 2011-04-17 : 13:59:14
I ran into this situation on our SQL server. Not a DBA, so a lot of this is new territory for me.
Of course, didn't discover this error until the last known good backup was scrubbed.
I have made a copy of the database to run scenarios on.

DBCC CHECKDB ('SiriusSQL_Training') WITH ALL_ERRORMSGS, NO_INFOMSGS

Msg 8933, Level 16, State 1, Line 1
Table error: Object ID 1705773134, index ID 1, partition ID 72057654755983360, alloc unit ID 72057654837837824 (type In-row data). The low key value on page (3:446138) (level 0) is not >= the key value in the parent (3:485080) slot 49.
Msg 8928, Level 16, State 1, Line 1
Object ID 1705773134, index ID 1, partition ID 72057654755983360, alloc unit ID 72057654837837824 (type In-row data): Page (3:446144) could not be processed. See other errors for details.
Msg 8976, Level 16, State 1, Line 1
Table error: Object ID 1705773134, index ID 1, partition ID 72057654755983360, alloc unit ID 72057654837837824 (type In-row data). Page (3:446144) was not seen in the scan although its parent (3:485080) and previous (3:499891) refer to it. Check any previous errors.
Msg 8944, Level 16, State 17, Line 1
Table error: Object ID 1705773134, index ID 1, partition ID 72057654755983360, alloc unit ID 72057654837837824 (type In-row data), page (3:446144), row 1. Test (columnOffsets->offTbl [varColumnNumber] <= (nextRec - pRec)) failed. Values are 428 and 427.
Msg 8944, Level 16, State 17, Line 1
Table error: Object ID 1705773134, index ID 1, partition ID 72057654755983360, alloc unit ID 72057654837837824 (type In-row data), page (3:446144), row 1. Test (columnOffsets->offTbl [varColumnNumber] <= (nextRec - pRec)) failed. Values are 428 and 427.
Msg 8934, Level 16, State 2, Line 1
Table error: Object ID 1705773134, index ID 1, partition ID 72057654755983360, alloc unit ID 72057654837837824 (type In-row data). The high key value on page (3:499885) (level 0) is not less than the low key value in the parent (0:1), slot 0 of the next page (3:446138).
Msg 8934, Level 16, State 3, Line 1
Table error: Object ID 1705773134, index ID 1, partition ID 72057654755983360, alloc unit ID 72057654837837824 (type In-row data). The high key value on page (3:499885) (level 0) is not less than the low key value in the parent (0:1), slot 0 of the next page (3:446138).
Msg 8978, Level 16, State 1, Line 1
Table error: Object ID 1705773134, index ID 1, partition ID 72057654755983360, alloc unit ID 72057654837837824 (type In-row data). Page (3:499893) is missing a reference from previous page (3:446144). Possible chain linkage problem.
CHECKDB found 0 allocation errors and 8 consistency errors in table 'transact' (object ID 1705773134).
CHECKDB found 0 allocation errors and 8 consistency errors in database 'SiriusSQL_Training'.
repair_allow_data_loss is the minimum repair level for the errors found by DBCC CHECKDB (SiriusSQL_Training).[/red]
Then I run with allow_data_loss and get....Msg 1505, Level 16, State 1, Line 1
The CREATE UNIQUE INDEX statement terminated because a duplicate key was found for the object name 'dbo.transact' and the index name 'transact_cluster'. The duplicate key value is (1222789).
Msg 1505, Level 16, State 1, Line 1
The CREATE UNIQUE INDEX statement terminated because a duplicate key was found for the object name 'dbo.transact' and the index name 'transact_trans_no'. The duplicate key value is (98042030000).
Msg 1505, Level 16, State 1, Line 1
The CREATE UNIQUE INDEX statement terminated because a duplicate key was found for the object name 'dbo.transact' and the index name 'sale_no'. The duplicate key value is (45505030000, 1222789).
Msg 1505, Level 16, State 1, Line 1
The CREATE UNIQUE INDEX statement terminated because a duplicate key was found for the object name 'dbo.transact' and the index name 'invoice_no'. The duplicate key value is (0, 1222796).
Msg 1505, Level 16, State 1, Line 1
The CREATE UNIQUE INDEX statement terminated because a duplicate key was found for the object name 'dbo.transact' and the index name 'departme'. The duplicate key value is (**TIPS** , **TIPS** , **TIPS** , 1222795).
Msg 1505, Level 16, State 1, Line 1
The CREATE UNIQUE INDEX statement terminated because a duplicate key was found for the object name 'dbo.transact' and the index name 'startdateitemreservno'. The duplicate key value is (<NULL>, **TRANS** , 0, 1222799).
Msg 1505, Level 16, State 1, Line 1
The CREATE UNIQUE INDEX statement terminated because a duplicate key was found for the object name 'dbo.transact' and the index name 'message'. The duplicate key value is (, 1222789).
Msg 1505, Level 16, State 1, Line 1
The CREATE UNIQUE INDEX statement terminated because a duplicate key was found for the object name 'dbo.transact' and the index name 'date_time'. The duplicate key value is (Dec 9 2009 8:24PM, Dec 9 2009 6:49PM, Dec 9 2009 6:49PM, Dec 9 2009 6:49PM, Dec 9 2009 6:49PM, Dec 9 2009 6:49PM, 1222789).
Msg 1505, Level 16, State 1, Line 1
The CREATE UNIQUE INDEX statement terminated because a duplicate key was found for the object name 'dbo.transact' and the index name 'to_revenue'. The duplicate key value is (<NULL>, 1222789).
Msg 1505, Level 16, State 1, Line 1
The CREATE UNIQUE INDEX statement terminated because a duplicate key was found for the object name 'dbo.transact' and the index name 'mastertran'. The duplicate key value is (98042030000, 1222789).
Msg 1505, Level 16, State 1, Line 1
The CREATE UNIQUE INDEX statement terminated because a duplicate key was found for the object name 'dbo.transact' and the index name 'saved_trno'. The duplicate key value is (0, 1222789).
Msg 1505, Level 16, State 1, Line 1
The CREATE UNIQUE INDEX statement terminated because a duplicate key was found for the object name 'dbo.transact' and the index name 'saved_slno'. The duplicate key value is (0, 1222789).
Msg 1505, Level 16, State 1, Line 1
The CREATE UNIQUE INDEX statement terminated because a duplicate key was found for the object name 'dbo.transact' and the index name 'invoiceextension'. The duplicate key value is (0, 2.50, 1222798).
Msg 1505, Level 16, State 1, Line 1
The CREATE UNIQUE INDEX statement terminated because a duplicate key was found for the object name 'dbo.transact' and the index name 'max4saleopt'. The duplicate key value is (**TIPS** , **TIPS** , **TIPS** , 45505030000, 1, Dec 9 2009 6:49PM, 1222795).
Msg 1505, Level 16, State 1, Line 1
The CREATE UNIQUE INDEX statement terminated because a duplicate key was found for the object name 'dbo.transact' and the index name 'pts4saleopt'. The duplicate key value is (0, 45505030000, 0.0000, Dec 9 2009 6:49PM, 1222789).
Msg 1505, Level 16, State 1, Line 1
The CREATE UNIQUE INDEX statement terminated because a duplicate key was found for the object name 'dbo.transact' and the index name 'reserv_no'. The duplicate key value is (0, 1222789).
Repair: The page (3:446144) has been deallocated from object ID 1705773134, index ID 1, partition ID 72057654755983360, alloc unit ID 72057654837837824 (type In-row data).
Msg 8945, Level 16, State 1, Line 1
Table error: Object ID 1705773134, index ID 1 will be rebuilt.
Could not repair this error.
Msg 8933, Level 16, State 1, Line 1
Table error: Object ID 1705773134, index ID 1, partition ID 72057654755983360, alloc unit ID 72057654837837824 (type In-row data). The low key value on page (3:446138) (level 0) is not >= the key value in the parent (3:485080) slot 49.
The error has been repaired.
Msg 8928, Level 16, State 1, Line 1
Object ID 1705773134, index ID 1, partition ID 72057654755983360, alloc unit ID 72057654837837824 (type In-row data): Page (3:446144) could not be processed. See other errors for details.
The error has been repaired.
Msg 8976, Level 16, State 1, Line 1
Table error: Object ID 1705773134, index ID 1, partition ID 72057654755983360, alloc unit ID 72057654837837824 (type In-row data). Page (3:446144) was not seen in the scan although its parent (3:485080) and previous (3:499891) refer to it. Check any previous errors.
The error has been repaired.
Msg 8944, Level 16, State 17, Line 1
Table error: Object ID 1705773134, index ID 1, partition ID 72057654755983360, alloc unit ID 72057654837837824 (type In-row data), page (3:446144), row 1. Test (columnOffsets->offTbl [varColumnNumber] <= (nextRec - pRec)) failed. Values are 428 and 427.
The error has been repaired.
Msg 8944, Level 16, State 17, Line 1
Table error: Object ID 1705773134, index ID 1, partition ID 72057654755983360, alloc unit ID 72057654837837824 (type In-row data), page (3:446144), row 1. Test (columnOffsets->offTbl [varColumnNumber] <= (nextRec - pRec)) failed. Values are 428 and 427.
The error has been repaired.
Msg 8934, Level 16, State 2, Line 1
Table error: Object ID 1705773134, index ID 1, partition ID 72057654755983360, alloc unit ID 72057654837837824 (type In-row data). The high key value on page (3:499885) (level 0) is not less than the low key value in the parent (0:1), slot 0 of the next page (3:446138).
The error has been repaired.
Msg 8934, Level 16, State 3, Line 1
Table error: Object ID 1705773134, index ID 1, partition ID 72057654755983360, alloc unit ID 72057654837837824 (type In-row data). The high key value on page (3:499885) (level 0) is not less than the low key value in the parent (0:1), slot 0 of the next page (3:446138).
The error has been repaired.
Msg 8978, Level 16, State 1, Line 1
Table error: Object ID 1705773134, index ID 1, partition ID 72057654755983360, alloc unit ID 72057654837837824 (type In-row data). Page (3:499893) is missing a reference from previous page (3:446144). Possible chain linkage problem.
The error has been repaired.
Msg 8945, Level 16, State 1, Line 1
Table error: Object ID 1705773134, index ID 2 will be rebuilt.
Could not repair this error.
Msg 8945, Level 16, State 1, Line 1
Table error: Object ID 1705773134, index ID 3 will be rebuilt.
Could not repair this error.
Msg 8945, Level 16, State 1, Line 1
Table error: Object ID 1705773134, index ID 4 will be rebuilt.
Could not repair this error.
Msg 8945, Level 16, State 1, Line 1
Table error: Object ID 1705773134, index ID 5 will be rebuilt.
Could not repair this error.
Msg 8945, Level 16, State 1, Line 1
Table error: Object ID 1705773134, index ID 6 will be rebuilt.
Could not repair this error.
Msg 8945, Level 16, State 1, Line 1
Table error: Object ID 1705773134, index ID 7 will be rebuilt.
Could not repair this error.
Msg 8945, Level 16, State 1, Line 1
Table error: Object ID 1705773134, index ID 8 will be rebuilt.
Could not repair this error.
Msg 8945, Level 16, State 1, Line 1
Table error: Object ID 1705773134, index ID 9 will be rebuilt.
Could not repair this error.
Msg 8945, Level 16, State 1, Line 1
Table error: Object ID 1705773134, index ID 10 will be rebuilt.
Could not repair this error.
Msg 8945, Level 16, State 1, Line 1
Table error: Object ID 1705773134, index ID 11 will be rebuilt.
Could not repair this error.
Msg 8945, Level 16, State 1, Line 1
Table error: Object ID 1705773134, index ID 12 will be rebuilt.
Could not repair this error.
Msg 8945, Level 16, State 1, Line 1
Table error: Object ID 1705773134, index ID 13 will be rebuilt.
Could not repair this error.
Msg 8945, Level 16, State 1, Line 1
Table error: Object ID 1705773134, index ID 14 will be rebuilt.
Could not repair this error.
Msg 8945, Level 16, State 1, Line 1
Table error: Object ID 1705773134, index ID 19 will be rebuilt.
Could not repair this error.
Msg 8945, Level 16, State 1, Line 1
Table error: Object ID 1705773134, index ID 37 will be rebuilt.
Could not repair this error.
CHECKDB found 0 allocation errors and 8 consistency errors in table 'transact' (object ID 1705773134).
CHECKDB fixed 0 allocation errors and 8 consistency errors in table 'transact' (object ID 1705773134).
CHECKDB found 0 allocation errors and 8 consistency errors in database 'SiriusSQL_Training'.
CHECKDB fixed 0 allocation errors and 8 consistency errors in database 'SiriusSQL_Training'.
The statement has been terminated.

...Then I run dbcc checktable ('transact') WITH ALL_ERRORMSGS, NO_INFOMSGS and get

Msg 8933, Level 16, State 1, Line 1
Table error: Object ID 1705773134, index ID 1, partition ID 72057654755983360, alloc unit ID 72057654837837824 (type In-row data). The low key value on page (3:446138) (level 0) is not >= the key value in the parent (3:485080) slot 49.
Msg 8976, Level 16, State 1, Line 1
Table error: Object ID 1705773134, index ID 1, partition ID 72057654755983360, alloc unit ID 72057654837837824 (type In-row data). Page (3:446144) was not seen in the scan although its parent (3:485080) and previous (3:499891) refer to it. Check any previous errors.
Msg 8934, Level 16, State 2, Line 1
Table error: Object ID 1705773134, index ID 1, partition ID 72057654755983360, alloc unit ID 72057654837837824 (type In-row data). The high key value on page (3:499885) (level 0) is not less than the low key value in the parent (0:1), slot 0 of the next page (3:446138).
Msg 8934, Level 16, State 3, Line 1
Table error: Object ID 1705773134, index ID 1, partition ID 72057654755983360, alloc unit ID 72057654837837824 (type In-row data). The high key value on page (3:499885) (level 0) is not less than the low key value in the parent (0:1), slot 0 of the next page (3:446138).
Msg 8978, Level 16, State 1, Line 1
Table error: Object ID 1705773134, index ID 1, partition ID 72057654755983360, alloc unit ID 72057654837837824 (type In-row data). Page (3:499893) is missing a reference from previous page (3:446144). Possible chain linkage problem.
CHECKTABLE found 0 allocation errors and 5 consistency errors in table 'transact' (object ID 1705773134).
repair_rebuild is the minimum repair level for the errors found by DBCC CHECKTABLE (SiriusSQL_Training.dbo.transact).
Then I run dbcc checktable ('transact', REPAIR_REBUILD)WITH ALL_ERRORMSGS, NO_INFOMSGS and get...

Msg 1505, Level 16, State 1, Line 1
The CREATE UNIQUE INDEX statement terminated because a duplicate key was found for the object name 'dbo.transact' and the index name 'transact_cluster'. The duplicate key value is (1222789).
Msg 1505, Level 16, State 1, Line 1
The CREATE UNIQUE INDEX statement terminated because a duplicate key was found for the object name 'dbo.transact' and the index name 'transact_trans_no'. The duplicate key value is (98042030000).
Msg 1505, Level 16, State 1, Line 1
The CREATE UNIQUE INDEX statement terminated because a duplicate key was found for the object name 'dbo.transact' and the index name 'sale_no'. The duplicate key value is (45505030000, 1222789).
Msg 1505, Level 16, State 1, Line 1
The CREATE UNIQUE INDEX statement terminated because a duplicate key was found for the object name 'dbo.transact' and the index name 'invoice_no'. The duplicate key value is (0, 1222796).
Msg 1505, Level 16, State 1, Line 1
The CREATE UNIQUE INDEX statement terminated because a duplicate key was found for the object name 'dbo.transact' and the index name 'departme'. The duplicate key value is (**TIPS** , **TIPS** , **TIPS** , 1222795).
Msg 1505, Level 16, State 1, Line 1
The CREATE UNIQUE INDEX statement terminated because a duplicate key was found for the object name 'dbo.transact' and the index name 'startdateitemreservno'. The duplicate key value is (<NULL>, **TRANS** , 0, 1222799).
Msg 1505, Level 16, State 1, Line 1
The CREATE UNIQUE INDEX statement terminated because a duplicate key was found for the object name 'dbo.transact' and the index name 'message'. The duplicate key value is (, 1222789).
Msg 1505, Level 16, State 1, Line 1
The CREATE UNIQUE INDEX statement terminated because a duplicate key was found for the object name 'dbo.transact' and the index name 'date_time'. The duplicate key value is (Dec 9 2009 8:24PM, Dec 9 2009 6:49PM, Dec 9 2009 6:49PM, Dec 9 2009 6:49PM, Dec 9 2009 6:49PM, Dec 9 2009 6:49PM, 1222789).
Msg 1505, Level 16, State 1, Line 1
The CREATE UNIQUE INDEX statement terminated because a duplicate key was found for the object name 'dbo.transact' and the index name 'to_revenue'. The duplicate key value is (<NULL>, 1222789).
Msg 1505, Level 16, State 1, Line 1
The CREATE UNIQUE INDEX statement terminated because a duplicate key was found for the object name 'dbo.transact' and the index name 'mastertran'. The duplicate key value is (98042030000, 1222789).
Msg 1505, Level 16, State 1, Line 1
The CREATE UNIQUE INDEX statement terminated because a duplicate key was found for the object name 'dbo.transact' and the index name 'saved_trno'. The duplicate key value is (0, 1222789).
Msg 1505, Level 16, State 1, Line 1
The CREATE UNIQUE INDEX statement terminated because a duplicate key was found for the object name 'dbo.transact' and the index name 'saved_slno'. The duplicate key value is (0, 1222789).
Msg 1505, Level 16, State 1, Line 1
The CREATE UNIQUE INDEX statement terminated because a duplicate key was found for the object name 'dbo.transact' and the index name 'invoiceextension'. The duplicate key value is (0, 2.50, 1222798).
Msg 1505, Level 16, State 1, Line 1
The CREATE UNIQUE INDEX statement terminated because a duplicate key was found for the object name 'dbo.transact' and the index name 'max4saleopt'. The duplicate key value is (**TIPS** , **TIPS** , **TIPS** , 45505030000, 1, Dec 9 2009 6:49PM, 1222795).
Msg 1505, Level 16, State 1, Line 1
The CREATE UNIQUE INDEX statement terminated because a duplicate key was found for the object name 'dbo.transact' and the index name 'pts4saleopt'. The duplicate key value is (0, 45505030000, 0.0000, Dec 9 2009 6:49PM, 1222789).
Msg 1505, Level 16, State 1, Line 1
The CREATE UNIQUE INDEX statement terminated because a duplicate key was found for the object name 'dbo.transact' and the index name 'reserv_no'. The duplicate key value is (0, 1222789).
Msg 8945, Level 16, State 1, Line 1
Table error: Object ID 1705773134, index ID 1 will be rebuilt.
Could not repair this error.
Msg 8933, Level 16, State 1, Line 1
Table error: Object ID 1705773134, index ID 1, partition ID 72057654755983360, alloc unit ID 72057654837837824 (type In-row data). The low key value on page (3:446138) (level 0) is not >= the key value in the parent (3:485080) slot 49.
The error has been repaired.
Msg 8976, Level 16, State 1, Line 1
Table error: Object ID 1705773134, index ID 1, partition ID 72057654755983360, alloc unit ID 72057654837837824 (type In-row data). Page (3:446144) was not seen in the scan although its parent (3:485080) and previous (3:499891) refer to it. Check any previous errors.
The error has been repaired.
Msg 8934, Level 16, State 2, Line 1
Table error: Object ID 1705773134, index ID 1, partition ID 72057654755983360, alloc unit ID 72057654837837824 (type In-row data). The high key value on page (3:499885) (level 0) is not less than the low key value in the parent (0:1), slot 0 of the next page (3:446138).
The error has been repaired.
Msg 8934, Level 16, State 3, Line 1
Table error: Object ID 1705773134, index ID 1, partition ID 72057654755983360, alloc unit ID 72057654837837824 (type In-row data). The high key value on page (3:499885) (level 0) is not less than the low key value in the parent (0:1), slot 0 of the next page (3:446138).
The error has been repaired.
Msg 8978, Level 16, State 1, Line 1
Table error: Object ID 1705773134, index ID 1, partition ID 72057654755983360, alloc unit ID 72057654837837824 (type In-row data). Page (3:499893) is missing a reference from previous page (3:446144). Possible chain linkage problem.
The error has been repaired.
Msg 8945, Level 16, State 1, Line 1
Table error: Object ID 1705773134, index ID 2 will be rebuilt.
Could not repair this error.
Msg 8945, Level 16, State 1, Line 1
Table error: Object ID 1705773134, index ID 3 will be rebuilt.
Could not repair this error.
Msg 8945, Level 16, State 1, Line 1
Table error: Object ID 1705773134, index ID 4 will be rebuilt.
Could not repair this error.
Msg 8945, Level 16, State 1, Line 1
Table error: Object ID 1705773134, index ID 5 will be rebuilt.
Could not repair this error.
Msg 8945, Level 16, State 1, Line 1
Table error: Object ID 1705773134, index ID 6 will be rebuilt.
Could not repair this error.
Msg 8945, Level 16, State 1, Line 1
Table error: Object ID 1705773134, index ID 7 will be rebuilt.
Could not repair this error.
Msg 8945, Level 16, State 1, Line 1
Table error: Object ID 1705773134, index ID 8 will be rebuilt.
Could not repair this error.
Msg 8945, Level 16, State 1, Line 1
Table error: Object ID 1705773134, index ID 9 will be rebuilt.
Could not repair this error.
Msg 8945, Level 16, State 1, Line 1
Table error: Object ID 1705773134, index ID 10 will be rebuilt.
Could not repair this error.
Msg 8945, Level 16, State 1, Line 1
Table error: Object ID 1705773134, index ID 11 will be rebuilt.
Could not repair this error.
Msg 8945, Level 16, State 1, Line 1
Table error: Object ID 1705773134, index ID 12 will be rebuilt.
Could not repair this error.
Msg 8945, Level 16, State 1, Line 1
Table error: Object ID 1705773134, index ID 13 will be rebuilt.
Could not repair this error.
Msg 8945, Level 16, State 1, Line 1
Table error: Object ID 1705773134, index ID 14 will be rebuilt.
Could not repair this error.
Msg 8945, Level 16, State 1, Line 1
Table error: Object ID 1705773134, index ID 19 will be rebuilt.
Could not repair this error.
Msg 8945, Level 16, State 1, Line 1
Table error: Object ID 1705773134, index ID 37 will be rebuilt.
Could not repair this error.
CHECKTABLE found 0 allocation errors and 5 consistency errors in table 'transact' (object ID 1705773134).
CHECKTABLE fixed 0 allocation errors and 5 consistency errors in table 'transact' (object ID 1705773134).
The statement has been terminated.

And this goes around and around and around. There were more errors initialy (8), but this is as good as it gets sofar. Any suggestions?

I have tried to drop and rebuild the index, but that went over baddly as its a clustered index. Tried deleting any records from the table with the same values in it. Did nothing.

Help appreciated.

GilaMonster
Master Smack Fu Yak Hacker

4507 Posts
Posted - 2011-04-17 : 14:51:45
Ok, can you please run the following, exactly as is, and post the full and complete results (not in red, that's hard to read)


DBCC CHECKDB (<Database Name>) WITH NO_INFOMSGS, ALL_ERRORMSGS


--
Gail Shaw
SQL Server MVP
Go to Top of Page

ferule
Starting Member

7 Posts
Posted - 2011-04-17 : 15:39:18
Msg 8933, Level 16, State 1, Line 1
Table error: Object ID 1705773134, index ID 1, partition ID 72057654755983360, alloc unit ID 72057654837837824 (type In-row data). The low key value on page (3:446138) (level 0) is not >= the key value in the parent (3:485080) slot 49.
Msg 8976, Level 16, State 1, Line 1
Table error: Object ID 1705773134, index ID 1, partition ID 72057654755983360, alloc unit ID 72057654837837824 (type In-row data). Page (3:446144) was not seen in the scan although its parent (3:485080) and previous (3:499891) refer to it. Check any previous errors.
Msg 8934, Level 16, State 2, Line 1
Table error: Object ID 1705773134, index ID 1, partition ID 72057654755983360, alloc unit ID 72057654837837824 (type In-row data). The high key value on page (3:499885) (level 0) is not less than the low key value in the parent (0:1), slot 0 of the next page (3:446138).
Msg 8934, Level 16, State 3, Line 1
Table error: Object ID 1705773134, index ID 1, partition ID 72057654755983360, alloc unit ID 72057654837837824 (type In-row data). The high key value on page (3:499885) (level 0) is not less than the low key value in the parent (0:1), slot 0 of the next page (3:446138).
Msg 8978, Level 16, State 1, Line 1
Table error: Object ID 1705773134, index ID 1, partition ID 72057654755983360, alloc unit ID 72057654837837824 (type In-row data). Page (3:499893) is missing a reference from previous page (3:446144). Possible chain linkage problem.
CHECKDB found 0 allocation errors and 5 consistency errors in table 'transact' (object ID 1705773134).
CHECKDB found 0 allocation errors and 5 consistency errors in database 'SiriusSQL_Training'.
repair_rebuild is the minimum repair level for the errors found by DBCC CHECKDB (SiriusSQL_Training).
Go to Top of Page

GilaMonster
Master Smack Fu Yak Hacker

4507 Posts
Posted - 2011-04-17 : 15:51:20
Try dropping the indexes that are referred to there (the clustered index on transact) and then run checkDB again with the same options.

--
Gail Shaw
SQL Server MVP
Go to Top of Page

ferule
Starting Member

7 Posts
Posted - 2011-04-17 : 16:07:18
I made an attempt to do that. Couldn't.

DROP index transact_cluster ON transact
Msg 3723, Level 16, State 5, Line 1
An explicit DROP INDEX is not allowed on index 'transact.transact_cluster'. It is being used for UNIQUE KEY constraint enforcement.

I tried a bit to drop the constraint but couldn't do that either.
I then 'disabled' the index, and tried to rebuild it. That left the table unreachable.
Cant run anything against the table since the index was dropped. Cant rebuild it, cause cant access the table....

What am I missing?
Go to Top of Page

GilaMonster
Master Smack Fu Yak Hacker

4507 Posts
Posted - 2011-04-17 : 16:27:44
No, no, no, no
You need to drop the constraint. ALTER TABLE ... DROP CONSTRAINT ... . If it gives errors, post the errors, I can't read your mind. :-)

Disabling the clustered index would make the table completely unavailable, it's clearly documented.

Please. Step by step, do nothing unless you know exactly what it is going to do.

--
Gail Shaw
SQL Server MVP
Go to Top of Page

ferule
Starting Member

7 Posts
Posted - 2011-04-17 : 16:47:02
I don't seem to know how to do that. The docs are confusing, as I am not a DBA and this is very uncharted territory for me...
This is a test database, so not overyly concerned...

ALTER TABLE transact DROP CONSTRAINT transact_cluster
Msg 1505, Level 16, State 1, Line 1
The CREATE UNIQUE INDEX statement terminated because a duplicate key was found for the object name 'dbo.transact' and the index name 'transact_trans_no'. The duplicate key value is (98044030000).
Msg 3727, Level 16, State 0, Line 1
Could not drop constraint. See previous errors.
The statement has been terminated.

Going forward.... (I know I am supposed to wait... but this seems logical)

ALTER TABLE transact DROP CONSTRAINT transact_trans_no
Msg 3725, Level 16, State 0, Line 1
The constraint 'transact_trans_no' is being referenced by table 'tr_info', foreign key constraint 'FK_tr_info_transact'.
Msg 3727, Level 16, State 0, Line 1
Could not drop constraint. See previous errors.

...I have a feeling this chain could go on, and on until there isn't any structure left.
At what point is it un-rebuildable. I have all the objects in the live database, and scripted. Being a novice though, I don't know how far I can keep going before it just goes kaput.
Go to Top of Page

GilaMonster
Master Smack Fu Yak Hacker

4507 Posts
Posted - 2011-04-17 : 17:38:54
Nothing irreparable here (that I can see). If you want to script and recreate the DB, go ahead, this can be fixed, it will take some data manipulation though, but then I suspect that script and recreate will also need that. Likely if you script and try to recreate the DB when you go to recreate the unique and pk constraint things will break.

Ok, first you need to drop all the foreign keys pointing to that table's primary key. (script them first so you can recreate) You'll need to do some querying of the system tables to identify all of them (sys.foreign_keys or you can just try to drop the pk and SQL will tell you one of the foreign keys, drop that and repeat until no more errors)

Once you've done that drop the primary key (it's a nonclustered primary key)
Then drop the unique constraint.
Then run a checkDB again (same options I gave you first time).

While you do all that, I'm going to bed. :-) (23h30 here)

p.s. If there's a DBA there, or someone that knows more about SQL then you do, get them involved.

p.p.s Keep note of everything you do, you'll have to do this to the actual database too.

--
Gail Shaw
SQL Server MVP
Go to Top of Page

ferule
Starting Member

7 Posts
Posted - 2011-04-17 : 18:05:10
Thanks!
I took a gamble and did all of that while I was waiting for your reply...
DBCC Check came back clean.

So now, I assume I need to recreate all of the links going backwards!
Will try and let you know.
Go to Top of Page

ferule
Starting Member

7 Posts
Posted - 2011-04-17 : 19:03:13
It worked!!!
Thanks again, needed the help.
If your ever on Mt Hood, in Oregon USA, look me up. Skiing 365 days a year up here...

Just because... here is the final solution to my issue.
For anyone else, obviously this has a lot of customization to my database, alter to suit your needs.

/* Drop Foreign Keys, then drop the two damaged index constraints */
/*
USE [SiriusSQL_Training]
GO
IF EXISTS (SELECT * FROM sys.foreign_keys WHERE object_id = OBJECT_ID(N'[dbo].[FK_r_entals_transact]') AND parent_object_id = OBJECT_ID(N'[dbo].[r_entals]'))
ALTER TABLE [dbo].[r_entals] DROP CONSTRAINT [FK_r_entals_transact]
*/

/*
USE [SiriusSQL_Training]
GO
IF EXISTS (SELECT * FROM sys.foreign_keys WHERE object_id = OBJECT_ID(N'[dbo].[FK_tr_info_transact]') AND parent_object_id = OBJECT_ID(N'[dbo].[tr_info]'))
ALTER TABLE [dbo].[tr_info] DROP CONSTRAINT [FK_tr_info_transact]
*/

--ALTER TABLE transact DROP CONSTRAINT transact_trans_no

--ALTER TABLE transact DROP CONSTRAINT transact_cluster

--DBCC CHECKDB ('SiriusSQL_Training') WITH ALL_ERRORMSGS, NO_INFOMSGS


/* Run this set of querries repeatedly, until no more dupes found. Delete the dupes */
/*
SELECT cluster, count(*)
FROM transact
GROUP BY cluster
HAVING count(*) > 1
*/

/*
set rowcount 1
delete from transact
where cluster = 1222802
GO
SELECT *
FROM transact
WHERE cluster = 1222802
*/

/* recreate the indexes, and the foreign keys */
/*
USE [SiriusSQL_Training]
GO
/****** Object: Index [transact_cluster] Script Date: 04/17/2011 15:07:36 ******/
ALTER TABLE [dbo].[transact] ADD CONSTRAINT [transact_cluster] UNIQUE CLUSTERED
(
[cluster] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 75) ON [SaleTransactions]
*/

/*
USE [SiriusSQL_Training]
GO
/****** Object: Index [transact_trans_no] Script Date: 04/17/2011 15:03:19 ******/
ALTER TABLE [dbo].[transact] ADD CONSTRAINT [transact_trans_no] PRIMARY KEY NONCLUSTERED
(
[trans_no] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 75) ON [SaleTransactions]
*/

/*
USE [SiriusSQL_Training]
GO
ALTER TABLE [dbo].[r_entals] WITH NOCHECK ADD CONSTRAINT [FK_r_entals_transact] FOREIGN KEY([trans_no])
REFERENCES [dbo].[transact] ([trans_no])
GO
ALTER TABLE [dbo].[r_entals] NOCHECK CONSTRAINT [FK_r_entals_transact]
*/

/*
USE [SiriusSQL_Training]
GO
ALTER TABLE [dbo].[tr_info] WITH NOCHECK ADD CONSTRAINT [FK_tr_info_transact] FOREIGN KEY([trans_no])
REFERENCES [dbo].[transact] ([trans_no])
ON DELETE CASCADE
NOT FOR REPLICATION
GO
ALTER TABLE [dbo].[tr_info] CHECK CONSTRAINT [FK_tr_info_transact]
*/

/*Final check....*/
--DBCC CHECKDB ('SiriusSQL_Training') WITH ALL_ERRORMSGS, NO_INFOMSGS

--All is good in the world.
Go to Top of Page

GilaMonster
Master Smack Fu Yak Hacker

4507 Posts
Posted - 2011-04-18 : 01:36:50
Pretty much, though I'm not sure I would have outright deleted those rows without further investigation. They could be rows that the corruption had doubled somehow, they could be rows that just had their pk value mangled. Personally I would have copied them to another table for further investigation.

Right, now that it's fixed, do so root-cause analysis. Look for IO-related errors and see if you can identify the cause.
Also, schedule integrity checks on a regular basis. I like to run them before a backup so that I know the DB is intact at time of backup

--
Gail Shaw
SQL Server MVP
Go to Top of Page

ferule
Starting Member

7 Posts
Posted - 2011-04-18 : 12:23:11
Good idea on the copy. I didn't think of that, since the data was from several years ago, and won't be missed. I was just too happy to have it work again.

I do have the maintenance plans in place, which is why I caught this error originaly. Unfortunatly I didn't realize what the error really meant. It took another two weeks before a user reported a problem.

Thanks again.
Go to Top of Page

Jahanzaib
Posting Yak Master

115 Posts
Posted - 2011-04-18 : 17:15:18
Drop index those creating an errors and then create again,that is an allocation or page issue

or you can rebuild the indexes


Regards,

Syed Jahanzaib Bin Hassan
MCTS,MCITP,OCA,OCP,OCE,SCJP,IBMCDBA

My Blog
www.aureus-salah.com
Go to Top of Page

tkizer
Almighty SQL Goddess

38200 Posts
Posted - 2011-04-18 : 17:17:21
Syed, what is the point of your post when it's already been handled by Gail?

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog
Go to Top of Page

GilaMonster
Master Smack Fu Yak Hacker

4507 Posts
Posted - 2011-04-18 : 17:27:30
Rebuild of the index would fail, as there were duplicate values in an index that should be unique. That's why Ferule had to drop the index, delete the duplicate rows and only then could he recreate the index.

--
Gail Shaw
SQL Server MVP
Go to Top of Page

Jahanzaib
Posting Yak Master

115 Posts
Posted - 2011-04-18 : 17:37:46
Unique index should have duplicate records ? is it possible ?

Regards,

Syed Jahanzaib Bin Hassan
MCTS,MCITP,OCA,OCP,OCE,SCJP,IBMCDBA

My Blog
www.aureus-salah.com
Go to Top of Page

GilaMonster
Master Smack Fu Yak Hacker

4507 Posts
Posted - 2011-04-18 : 17:44:23
With corruption, yes. That's why CheckDB throws errors when it finds that. If you'd read through the errors...

quote:

Then I run dbcc checktable ('transact', REPAIR_REBUILD)WITH ALL_ERRORMSGS, NO_INFOMSGS and get...

Msg 1505, Level 16, State 1, Line 1
The CREATE UNIQUE INDEX statement terminated because a duplicate key was found for the object name 'dbo.transact' and the index name 'transact_cluster'. The duplicate key value is (1222789).
Msg 1505, Level 16, State 1, Line 1
The CREATE UNIQUE INDEX statement terminated because a duplicate key was found for the object name 'dbo.transact' and the index name 'transact_trans_no'. The duplicate key value is (98042030000).
Msg 1505, Level 16, State 1, Line 1
The CREATE UNIQUE INDEX statement terminated because a duplicate key was found for the object name 'dbo.transact' and the index name 'sale_no'. The duplicate key value is (45505030000, 1222789).
Msg 1505, Level 16, State 1, Line 1
The CREATE UNIQUE INDEX statement terminated because a duplicate key was found for the object name 'dbo.transact' and the index name 'invoice_no'. The duplicate key value is (0, 1222796).


--
Gail Shaw
SQL Server MVP
Go to Top of Page

Jahanzaib
Posting Yak Master

115 Posts
Posted - 2011-04-18 : 18:21:22
amazing...........

Regards,

Syed Jahanzaib Bin Hassan
MCTS,MCITP,OCA,OCP,OCE,SCJP,IBMCDBA

My Blog
www.aureus-salah.com
Go to Top of Page
   

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources

Articles

Forums

Blogs

Contact Us

About the Site