October 4, 2012 at 7:26 am
TJ-356724 (10/4/2012)
2000 RESULTSServer: Msg 8951, Level 16, State 1, Line 1
Table error: Table 'AssignmentRoot' (ID 1286229770). Missing or invalid key in index 'idx_AssignmentRoot_RepName' (ID 70) for the row:
Server: Msg 8955, Level 16, State 1, Line 1
Data row (1:1086898:0) identified by (RID = (1:1086898:0) ItemID = 738417) has index values (RepName = 'House_PV' and ItemID = 738417).
CHECKDB found 0 allocation errors and 1 consistency errors in table 'AssignmentRoot' (object ID 1286229770).
CHECKDB found 0 allocation errors and 1 consistency errors in database 'Tworks'.
repair_fast is the minimum repair level for the errors found by DBCC CHECKDB (Tworks ).
2008 RESULTS
Msg 8951, Level 16, State 1, Line 1
Table error: table 'AssignmentRoot' (ID 1286229770). Data row does not have a matching index row in the index 'idx_AssignmentRoot_RepName' (ID 70). Possible missing or invalid keys for the index row matching:
Msg 8955, Level 16, State 1, Line 1
Data row (1:1086898:0) identified by (ItemID = 738417) with index values 'RepName = 'House_PV' and ItemID = 738417'.
CHECKDB found 0 allocation errors and 1 consistency errors in table 'AssignmentRoot' (object ID 1286229770).
CHECKDB found 0 allocation errors and 1 consistency errors in database 'Tworks'.
repair_rebuild is the minimum repair level for the errors found by DBCC CHECKDB (Tworks).
On the 2008 server make sure to run DBCC CHECKDB WITH DATA_PURITY this will check for any column to data mismatches, which it sounds like you have here.
Perform a select on the table based on the 'RepName = 'House_PV' and ItemID = '738417'. Check the column values and correct any that seem wrong.
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
October 4, 2012 at 7:51 am
We find nothing wrong with data and are not opposed to rebuilding the entire index this weekend.
October 4, 2012 at 8:30 am
On the 2008 server make sure to run DBCC CHECKDB WITH DATA_PURITY this will check for any column to data mismatches, which it sounds like you have here.
Perform a select on the table based on the 'RepName = 'House_PV' and ItemID = '738417'. Check the column values and correct any that seem wrong.
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
October 4, 2012 at 8:51 am
running now
October 4, 2012 at 9:00 am
I ran DBCC CHECKDB('TWORKS') WITH data_purity, all_errormsgs, NO_INFOMSGS on 2008
The result:
Msg 8951, Level 16, State 1, Line 1
Table error: table 'AssignmentRoot' (ID 1286229770). Data row does not have a matching index row in the index 'idx_AssignmentRoot_RepName' (ID 70). Possible missing or invalid keys for the index row matching:
Msg 8955, Level 16, State 1, Line 1
Data row (1:1086898:0) identified by (ItemID = 738417) with index values 'RepName = 'House_PV' and ItemID = 738417'.
CHECKDB found 0 allocation errors and 1 consistency errors in table 'AssignmentRoot' (object ID 1286229770).
CHECKDB found 0 allocation errors and 1 consistency errors in database 'Tworks'.
repair_rebuild is the minimum repair level for the errors found by DBCC CHECKDB (Tworks).
We ran
select * from AssignmentRoot where ItemID = 738417 and RepName = 'House_PV'
and reviewed the data. Nothing in particular jumps out at us.
October 4, 2012 at 9:53 am
No, you won't see anything wrong with that query. The cause of the problem is that the nonclustered index is missing a row
Drop the nonclustered index idx_AssignmentRoot_RepName and then recreate it (on SQL 2000). Not rebuild, drop and create. Once done (on SQL 2000) run checkDB again.
Don't worry about data purity for the moment, that you can do after you fix all the schema and consistency corruption. The error you're getting here is not a data purity error, it's corruption in the nonclustered index.
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
October 4, 2012 at 9:59 am
Thanks you very much for the help and it's exactly where I was pointing and you confirmed. I'll do early tomorrow morning and advise.
You and Perry have been a great help!!
October 4, 2012 at 9:37 pm
idex was dropped and recreated. checkdb returned zero errors
October 4, 2012 at 11:43 pm
Well done, thanks for posting back
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
Viewing 9 posts - 31 through 38 (of 38 total)
You must be logged in to reply to this topic. Login to reply