SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 Data Corruption Issues
 Corruption in Clustered Index and more
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

ferule
Starting Member

7 Posts

Posted - 04/17/2011 :  13:59:14  Show Profile  Reply with Quote
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.

Edited by - ferule on 04/17/2011 15:08:36

GilaMonster
Flowing Fount of Yak Knowledge

South Africa
4507 Posts

Posted - 04/17/2011 :  14:51:45  Show Profile  Visit GilaMonster's Homepage  Reply with Quote
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 - 04/17/2011 :  15:39:18  Show Profile  Reply with Quote
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
Flowing Fount of Yak Knowledge

South Africa
4507 Posts

Posted - 04/17/2011 :  15:51:20  Show Profile  Visit GilaMonster's Homepage  Reply with Quote
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 - 04/17/2011 :  16:07:18  Show Profile  Reply with Quote
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
Flowing Fount of Yak Knowledge

South Africa
4507 Posts

Posted - 04/17/2011 :  16:27:44  Show Profile  Visit GilaMonster's Homepage  Reply with Quote
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

Edited by - GilaMonster on 04/17/2011 16:31:19
Go to Top of Page

ferule
Starting Member

7 Posts

Posted - 04/17/2011 :  16:47:02  Show Profile  Reply with Quote
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.

Edited by - ferule on 04/17/2011 17:27:42
Go to Top of Page

GilaMonster
Flowing Fount of Yak Knowledge

South Africa
4507 Posts

Posted - 04/17/2011 :  17:38:54  Show Profile  Visit GilaMonster's Homepage  Reply with Quote
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 - 04/17/2011 :  18:05:10  Show Profile  Reply with Quote
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 - 04/17/2011 :  19:03:13  Show Profile  Reply with Quote
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
Flowing Fount of Yak Knowledge

South Africa
4507 Posts

Posted - 04/18/2011 :  01:36:50  Show Profile  Visit GilaMonster's Homepage  Reply with Quote
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 - 04/18/2011 :  12:23:11  Show Profile  Reply with Quote
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

Pakistan
115 Posts

Posted - 04/18/2011 :  17:15:18  Show Profile  Visit Jahanzaib's Homepage  Reply with Quote
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

USA
37157 Posts

Posted - 04/18/2011 :  17:17:21  Show Profile  Visit tkizer's Homepage  Reply with Quote
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
Flowing Fount of Yak Knowledge

South Africa
4507 Posts

Posted - 04/18/2011 :  17:27:30  Show Profile  Visit GilaMonster's Homepage  Reply with Quote
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

Pakistan
115 Posts

Posted - 04/18/2011 :  17:37:46  Show Profile  Visit Jahanzaib's Homepage  Reply with Quote
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
Flowing Fount of Yak Knowledge

South Africa
4507 Posts

Posted - 04/18/2011 :  17:44:23  Show Profile  Visit GilaMonster's Homepage  Reply with Quote
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

Edited by - GilaMonster on 04/18/2011 17:45:20
Go to Top of Page

Jahanzaib
Posting Yak Master

Pakistan
115 Posts

Posted - 04/18/2011 :  18:21:22  Show Profile  Visit Jahanzaib's Homepage  Reply with Quote
amazing...........

Regards,

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

My Blog
www.aureus-salah.com
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.36 seconds. Powered By: Snitz Forums 2000