Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 12»»

dbcc check db errors Expand / Collapse
Author
Message
Posted Monday, January 31, 2011 7:50 AM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Yesterday @ 11:04 AM
Points: 1,293, Visits: 2,859
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 ).



Post #1056204
Posted Monday, January 31, 2011 8:33 AM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Yesterday @ 6:05 AM
Points: 40,258, Visits: 36,681
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 2008, MVP
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Post #1056229
Posted Monday, January 31, 2011 9:35 AM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Yesterday @ 11:04 AM
Points: 1,293, Visits: 2,859
Thanks for your quick reply. However, I ran a rebuild all indexes and DBCC CHECK DB and it came back with zero errors now.


Post #1056266
Posted Monday, January 31, 2011 10:02 AM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Yesterday @ 6:05 AM
Points: 40,258, Visits: 36,681
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 2008, MVP
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Post #1056279
Posted Monday, January 31, 2011 10:03 AM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Yesterday @ 11:04 AM
Points: 1,293, Visits: 2,859
Thanks. I am going to have the server engineer team runs some diagnostics on the server to make sure everything checks out OK.


Post #1056281
Posted Monday, January 31, 2011 10:22 AM


SSC-Insane

SSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-Insane

Group: General Forum Members
Last Login: Wednesday, October 8, 2014 4:15 AM
Points: 20,581, Visits: 9,619
For a more step by step guide written by Gail :


Help, my database is corrupt. Now what?
Post #1056292
Posted Thursday, February 3, 2011 7:18 AM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Yesterday @ 11:04 AM
Points: 1,293, Visits: 2,859
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.



Post #1058069
Posted Thursday, February 3, 2011 7:48 AM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Yesterday @ 6:05 AM
Points: 40,258, Visits: 36,681
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 2008, MVP
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Post #1058087
Posted Thursday, February 3, 2011 8:13 AM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Yesterday @ 11:04 AM
Points: 1,293, Visits: 2,859
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.



Post #1058111
Posted Thursday, February 3, 2011 8:19 AM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Yesterday @ 6:05 AM
Points: 40,258, Visits: 36,681
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 2008, MVP
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Post #1058119
« Prev Topic | Next Topic »

Add to briefcase 12»»

Permissions Expand / Collapse