January 31, 2011 at 7:50 am
SQL Server 2000.... How do I find what tables these problem objects are?
[Microsoft][ODBC SQL Server Driver][SQL Server]Table error: Table '(Object ID 324208951)' (ID 324208951). Missing or invalid key in index '(Index ID 5)' (ID 5) for the row:
[Microsoft][ODBC SQL Server Driver][SQL Server]Table error: Table '(Object ID 324208951)' (ID 324208951). Missing or invalid key in index '(Index ID 5)' (ID 5) for the row:
[Microsoft][ODBC SQL Server Driver][SQL Server]Data row (1:2569152:49) identified by (RID = (1:2569152:49) ) has index values (DMA_NBR = 678 and FRANID = '102800').
[Microsoft][ODBC SQL Server Driver][SQL Server]Data row (1:2569152:69) identified by (RID = (1:2569152:69) ) has index values (DMA_NBR = 679 and FRANID = '105800').
[Microsoft][ODBC SQL Server Driver][SQL Server]Table error: Table '(Object ID 324208951)' (ID 324208951). Missing or invalid key in index '(Index ID 5)' (ID 5) for the row:
[Microsoft][ODBC SQL Server Driver][SQL Server]CHECKDB found 0 allocation errors and 36 consistency errors in database 'AFA_MKIS'.
[Microsoft][ODBC SQL Server Driver][SQL Server]repair_fast is the minimum repair level for the errors found by DBCC CHECKDB (AFA_MKIS ).
January 31, 2011 at 8:33 am
For the first one
SELECT name FROM sysobjects WHERE id = 324208951
Of course, if there's any metadata damage, the table may not be identifiable.
For the second one (Data row (1:2569152:49) identified by (RID = (1:2569152:49) ) ), the second of the numbers is the page no. Use DBCC page to examine the header.
DBCC TRACEON(3604)
DBCC PAGE(<database id>,1,2569152)
DBCC TRACEOFF(3604)
There should be an objectid in the header that you can then look up to sysobjects
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
January 31, 2011 at 9:35 am
Thanks for your quick reply. However, I ran a rebuild all indexes and DBCC CHECK DB and it came back with zero errors now.
January 31, 2011 at 10:02 am
Looking at the errors, that's reasonable, they appear to be nonclustered index-related errors. Worth noting that rebuilding indexes is not a general solution.
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
January 31, 2011 at 10:03 am
Thanks. I am going to have the server engineer team runs some diagnostics on the server to make sure everything checks out OK.
January 31, 2011 at 10:22 am
February 3, 2011 at 7:18 am
I found another db that we inherited and no one had a CHECKDB scheduled. I ran it on this db and found this error:
Server: Msg 8964, Level 16, State 1, Line 1
Table error: Object ID 2. The text, ntext, or image node at page (1:87), slot 7, text ID 95630196736 is not referenced.
CHECKDB found 0 allocation errors and 1 consistency errors in database 'PHASE2'.
repair_allow_data_loss is the minimum repair level for the errors found by DBCC CHECKDB (PHASE2 ).
SELECT name FROM sysobjects WHERE id = 2
sysindexes
(1 row(s) affected)
So it appears that the consistency error is in the sysindexes table? How serious of a problem is this and will the repair_allow_data-loss only effect the sysindexes table and not the application db data.
February 3, 2011 at 7:48 am
Repair won't touch the system tables. Fortunately there a solution that might work.
http://sqlinthewild.co.za/index.php/2009/08/24/stats-blob-corruptions/
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
February 3, 2011 at 8:13 am
Great link, however, if I follow the link and attempt to do it it won't work because the index is on a system table and when you try and drop an index on a system table it gives you a message saying it is a system table and you cannot drop an index on a system table. I just might have to call Microsoft on this one.
I have no idea how long this has been this way since we inherited this SQL Server from a company that had this running with no real DBA on staff and no integrity check jobs were being run. This database has been around since at least 2004 so no telling how long this has been this way or it may have even been a SQL Server 7 db before a SQL 2000 too.
February 3, 2011 at 8:19 am
Markus (2/3/2011)
Great link, however, if I follow the link and attempt to do it it won't work because the index is on a system table and when you try and drop an index on a system table it gives you a message saying it is a system table and you cannot drop an index on a system table. I just might have to call Microsoft on this one.
It's not about an index on a system table. sysindexes contains all the indexes in the system.
Please run the following and post the entire results.
DBCC CHECKDB (<Database Name>) WITH NO_INFOMSGS, ALL_ERRORMSGS
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
February 3, 2011 at 8:25 am
Here is the output.
Table error: Object ID 2. The text, ntext, or image node at page (1:87), slot 7, text ID 95630196736 is not referenced.
CHECKDB found 0 allocation errors and 1 consistency errors in table 'sysindexes' (object ID 2).
CHECKDB found 0 allocation errors and 1 consistency errors in database 'PHASE2'.
repair_allow_data_loss is the minimum repair level for the errors found by DBCC CHECKDB (PHASE2 ).
The reason I thought it was a system table/index is because of this:
SELECT name FROM sysobjects WHERE id = 2
sysindexes
(1 row(s) affected)
February 3, 2011 at 9:14 am
Yes, sysindexes is a system table, but my post doesn't say to drop indexes off of a system table. Sysindexes contains all indexes and so in general the fix for text corruption in sysindexes is to work out which index (on a user table) the corrupt row refers to and to drop that index.
Unfortunately that's not going to work in this case, because it's an orphaned stats blob (one without a row), rather than a corrupt stats blob. Didn't notice that initially. I don't think this is repairable short of script, export, recreate. That said, it's not a fatal corruption, it shouldn't cause further problems. CheckDB will just fail every time it's run.
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
February 3, 2011 at 9:20 am
GilaMonster (2/3/2011)
Yes, sysindexes is a system table, but my post doesn't say to drop indexes off of a system table. Sysindexes contains all indexes and so in general the fix for text corruption in sysindexes is to work out which index (on a user table) the corrupt row refers to and to drop that index.Unfortunately that's not going to work in this case, because it's an orphaned stats blob (one without a row), rather than a corrupt stats blob. Didn't notice that initially. I don't think this is repairable short of script, export, recreate. That said, it's not a fatal corruption, it shouldn't cause further problems. CheckDB will just fail every time it's run.
OK thanks a million. I was following the link you posted and attempting to follow that. That is why I thought I was heading down the path of the system table index.
I guess this is a good example of a company running SQL Server for years without a true DBA since no Integrity checks have been running. I found a db backup from back in August and it has the same corruption. My guess this has been in the database for years.
Thanks again.
Viewing 13 posts - 1 through 12 (of 12 total)
You must be logged in to reply to this topic. Login to reply