dbcc check db errors

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » SQL Server 7,2000 » Data Corruption » dbcc check db errors

13 posts, Page 1 of 2

1 2 »»

dbcc check db errors

Rate Topic

Display Mode

Topic Options

Author

Message

Markus

Posted Monday, January 31, 2011 7:50 AM

Ten Centuries

Group: General Forum Members
Last Login: Today @ 5:14 AM
Points: 1,048, Visits: 2,216

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

GilaMonster

Posted Monday, January 31, 2011 8:33 AM

SSC-Dedicated

Group: General Forum Members
Last Login: Today @ 2:07 PM
Points: 37,731, Visits: 29,994

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

Markus

Posted Monday, January 31, 2011 9:35 AM

Ten Centuries

Group: General Forum Members
Last Login: Today @ 5:14 AM
Points: 1,048, Visits: 2,216

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

GilaMonster

Posted Monday, January 31, 2011 10:02 AM

SSC-Dedicated

Group: General Forum Members
Last Login: Today @ 2:07 PM
Points: 37,731, Visits: 29,994

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

Markus

Posted Monday, January 31, 2011 10:03 AM

Ten Centuries

Group: General Forum Members
Last Login: Today @ 5:14 AM
Points: 1,048, Visits: 2,216

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

Ninja's_RGR'us

Posted Monday, January 31, 2011 10:22 AM

SSC-Insane

Group: General Forum Members
Last Login: Yesterday @ 2:27 PM
Points: 21,357, Visits: 9,537

For a more step by step guide written by Gail :

Help, my database is corrupt. Now what?

Post #1056292

Markus

Posted Thursday, February 03, 2011 7:18 AM

Ten Centuries

Group: General Forum Members
Last Login: Today @ 5:14 AM
Points: 1,048, Visits: 2,216

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

GilaMonster

Posted Thursday, February 03, 2011 7:48 AM

SSC-Dedicated

Group: General Forum Members
Last Login: Today @ 2:07 PM
Points: 37,731, Visits: 29,994

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

Markus

Posted Thursday, February 03, 2011 8:13 AM

Ten Centuries

Group: General Forum Members
Last Login: Today @ 5:14 AM
Points: 1,048, Visits: 2,216

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

GilaMonster

Posted Thursday, February 03, 2011 8:19 AM

SSC-Dedicated

Group: General Forum Members
Last Login: Today @ 2:07 PM
Points: 37,731, Visits: 29,994

Markus (2/3/2011)

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 »

13 posts, Page 1 of 2

1 2 »»

Permissions