January 12, 2010 at 4:33 pm
One of my databases has integrity issues. I'm running CHECKDB through a maintenance plan and it is producing the following error:
Executing the query "DBCC CHECKDB(N'MYDB1') WITH NO_INFOMSGS
" failed with the following error: "Table error: table 'MYTABLE1' (ID 430624577). Data row does not have a matching index row in the index 'IDX_MYTABLE1_2' (ID 22). Possible missing or invalid keys for the index row matching:
Data row (1:738450:15) identified by (ResultID = 88754384) with index values 'DateMeasurement = '2009-12-09 01:00:00.000' and VisibleFlag = 1 and Copied = 1 and ResultEventID = 4139317 and ResultID = 88754384'.
Table error: table 'MYTABLE1' (ID 430624577). Data row does not have a matching index row in the index 'IDX_MYTABLE1_2' (ID 22). Possible missing or invalid keys for the index row matching:
Data row (1:738450:16) identified by (ResultID = 88754385) with index values 'DateMeasurement = '2009-12-09 00:00:00.000' and VisibleFlag = 1 and Copied = 1 and ResultEventID = 4139317 and ResultID = 88754385'.
Table error: table 'MYTABLE1' (ID 430624577). Data row does not have a matching index row in the index 'IDX_MYTABLE1_2' (ID 22). Possible missing or invalid keys for the index row matching:
Data row (1:738450:17) identified by (ResultID = 88754386) with index values 'DateMeasurement = '2009-12-08 23:00:00.000' and VisibleFlag = 1 and Copied = 1 and ResultEventID = 4139317 and ResultID = 88754386'.
Table error: table 'MYTABLE1' (ID 430624577). Data row does not have a matching index row in the index 'IDX_MYTABLE1_2' (ID 22). Possible missing or invalid keys for the index row matching:
Data row (1:738450:18) identified by (ResultID = 88754387) with index values 'DateMeasurement = '2009-12-08 22:00:00.000' and VisibleFlag = 1 and Copied = 1 and ResultEventID = 4139317 and ResultID = 88754387'.
Table error: table 'MYTABLE1' (ID 430624577). Data row does not have a matching index row in the index 'IDX_MYTABLE1_2' (ID 22). Possible missing or invalid keys for the index row matching:
Data row (1:738450:19) identified by (ResultID = 88754388) with index values 'DateMeasurement = '2009-12-08 21:00:00.000' and VisibleFlag = 1 and Copied = 1 and ResultEventID = 4139317 and ResultID = 88754388'.
Table error: table 'MYTABLE1' (ID 430624577). Data row does not have a matching index row in the index 'IDX_MYTABLE1_2' (ID 22). Possible missing or invalid keys for the index row matching:
Data row (1:738450:20) identified by (ResultID = 88754389) with index values 'DateMeasurement = '2009-12-08 20:00:00.000' and VisibleFlag = 1 and Copied = 1 and ResultEventID = 4139317 and ResultID = 88754389'.
Table error: table 'MYTABLE1' (ID 430624577). Data row does not have a matching index row in the index 'IDX_MYTABLE1_2' (ID 22). Possible missing or invalid keys for the index row matching:
Data row (1:738450:21) identified by (ResultID = 88754390) with index values 'DateMeasurement = '2009-12-08 19:00:00.000' and VisibleFlag = 1 and Copied = 1 and ResultEventID = 4139317 and ResultID = 88754390'.
Table error: table 'MYTABLE1' (ID 430624577). Data row does not have a matching index row in the index 'IDX_MYTABLE1_2' (ID 22). Possible missing or invalid keys for the index row matching:
Data row (1:738450:22) identified by (ResultID = 88754391) with index values 'DateMeasurement = '2009-12-08 18:00:00.000' and VisibleFlag = 1 and Copied = 1 and ResultEventID = 4139317 and ResultID = 88754391'.
Table error: table 'MYTABLE1' (ID 430624577). Data row does not have a matching index row in the index 'IDX_MYTABLE1_2' (ID 22). Possible missing or invalid keys for the index row matching:
Data row (1:738450:23) identified by (ResultID = 88754392) with index values 'DateMeasurement = '2009-12-08 17:00:00.000' and VisibleFlag = 1 and Copied = 1 and ResultEventID = 4139317 and ResultID = 88754392'.
Table error: table 'MYTABLE1' (ID 430624577). Data row does not have a matching index row in the index 'IDX_MYTABLE1_2' (ID 22). Possible missing or invalid keys for the index row matching:
Data row (1:738450:24) identified by (ResultID = 88754393) with index values 'DateMeasurement = '2009-12-08 16:00:00.000' and VisibleFlag = 1 and Copied = 1 and ResultEventID = 4139317 and ResultID = 88754393'.
Table error: table 'MYTABLE1' (ID 430624577). Data row does not have a matching index row in the index 'IDX_MYTABLE1_2' (ID 22). Possible missing or invalid keys for the index row matching:
Data row (1:738450:25) identified by (ResultID = 88754394) with index values 'DateMeasurement = '2009-12-08 15:00:00.000' and VisibleFlag = 1 and Copied = 1 and ResultEventID = 4139317 and ResultID = 88754394'.
Table error: table 'MYTABLE1' (ID 430624577). Data row does not have a matching index row in the index 'IDX_MYTABLE1_2' (ID 22). Possible missing or invalid keys for the index row matching:
Data row (1:738450:33) identified by (ResultID = 88754402) with index values 'DateMeasurement = '2009-12-09 02:00:00.000' and VisibleFlag = 1 and Copied = 1 and ResultEventID = 4139317 and ResultID = 88754402'.
Table error: table 'MYTABLE1' (ID 430624577). Data row does not have a matching index row in the index 'IDX_MYTABLE1_2' (ID 22). Possible missing or invalid keys for the index row matching:
Data row (1:738450:90) identified by (ResultID = 88754459) with index values 'DateMeasurement = '2009-12-09 03:00:00.000' and VisibleFlag = 1 and Copied = 1 and ResultEventID = 4139320 and ResultID = 88754459'.
CHECKDB found 0 allocation errors and 13 consistency errors in table 'MYTABLE1' (object ID 430624577).
CHECKDB found 0 allocation errors and 13 consistency errors in database 'MYDB1'.
repair_rebuild is the minimum repair level for the errors found by DBCC CHECKDB (MYDB1).". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly.
Urgently need ideas on what I do to fix it!
Thanks
January 12, 2010 at 7:00 pm
I would try rebuilding that index before doing anything else. All of those errors are related to the same index (it looks like) and it appears that the index is a non clustered index.
You should be able to drop and recreate the index with no data loss.
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
January 13, 2010 at 12:00 am
i think You need to do this
1.Take the backup of table
2.drop and recreate the indexes
3.check table consistency with DBCC Checktable.
if found the same errors
1.import data from that table to new table
2.create cluster index if possible
3.check table consistency with DBCC Checktable
Regards,
Shivrudra W
January 13, 2010 at 12:08 am
Fortunately the damage here is all in a nonclustered index and hence completely repairable.
Drop the index IDX_MYTABLE1_2 on MYTABLE1 and recreate it. Do not rebuild it, that may fail due to the corruption. Drop the index completely, then create it again.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply