SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


dbcc check db errors


dbcc check db errors

Author
Message
Markus
Markus
Hall of Fame
Hall of Fame (3K reputation)Hall of Fame (3K reputation)Hall of Fame (3K reputation)Hall of Fame (3K reputation)Hall of Fame (3K reputation)Hall of Fame (3K reputation)Hall of Fame (3K reputation)Hall of Fame (3K reputation)

Group: General Forum Members
Points: 3039 Visits: 3775
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 ).



GilaMonster
GilaMonster
SSC Guru
SSC Guru (88K reputation)SSC Guru (88K reputation)SSC Guru (88K reputation)SSC Guru (88K reputation)SSC Guru (88K reputation)SSC Guru (88K reputation)SSC Guru (88K reputation)SSC Guru (88K reputation)

Group: General Forum Members
Points: 88397 Visits: 45283
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

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


Markus
Markus
Hall of Fame
Hall of Fame (3K reputation)Hall of Fame (3K reputation)Hall of Fame (3K reputation)Hall of Fame (3K reputation)Hall of Fame (3K reputation)Hall of Fame (3K reputation)Hall of Fame (3K reputation)Hall of Fame (3K reputation)

Group: General Forum Members
Points: 3039 Visits: 3775
Thanks for your quick reply. However, I ran a rebuild all indexes and DBCC CHECK DB and it came back with zero errors now.



GilaMonster
GilaMonster
SSC Guru
SSC Guru (88K reputation)SSC Guru (88K reputation)SSC Guru (88K reputation)SSC Guru (88K reputation)SSC Guru (88K reputation)SSC Guru (88K reputation)SSC Guru (88K reputation)SSC Guru (88K reputation)

Group: General Forum Members
Points: 88397 Visits: 45283
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

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


Markus
Markus
Hall of Fame
Hall of Fame (3K reputation)Hall of Fame (3K reputation)Hall of Fame (3K reputation)Hall of Fame (3K reputation)Hall of Fame (3K reputation)Hall of Fame (3K reputation)Hall of Fame (3K reputation)Hall of Fame (3K reputation)

Group: General Forum Members
Points: 3039 Visits: 3775
Thanks. I am going to have the server engineer team runs some diagnostics on the server to make sure everything checks out OK.



Ninja's_RGR'us
Ninja's_RGR'us
One Orange Chip
One Orange Chip (29K reputation)One Orange Chip (29K reputation)One Orange Chip (29K reputation)One Orange Chip (29K reputation)One Orange Chip (29K reputation)One Orange Chip (29K reputation)One Orange Chip (29K reputation)One Orange Chip (29K reputation)

Group: General Forum Members
Points: 29307 Visits: 9671
For a more step by step guide written by Gail :


Help, my database is corrupt. Now what?
Markus
Markus
Hall of Fame
Hall of Fame (3K reputation)Hall of Fame (3K reputation)Hall of Fame (3K reputation)Hall of Fame (3K reputation)Hall of Fame (3K reputation)Hall of Fame (3K reputation)Hall of Fame (3K reputation)Hall of Fame (3K reputation)

Group: General Forum Members
Points: 3039 Visits: 3775
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.



GilaMonster
GilaMonster
SSC Guru
SSC Guru (88K reputation)SSC Guru (88K reputation)SSC Guru (88K reputation)SSC Guru (88K reputation)SSC Guru (88K reputation)SSC Guru (88K reputation)SSC Guru (88K reputation)SSC Guru (88K reputation)

Group: General Forum Members
Points: 88397 Visits: 45283
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

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


Markus
Markus
Hall of Fame
Hall of Fame (3K reputation)Hall of Fame (3K reputation)Hall of Fame (3K reputation)Hall of Fame (3K reputation)Hall of Fame (3K reputation)Hall of Fame (3K reputation)Hall of Fame (3K reputation)Hall of Fame (3K reputation)

Group: General Forum Members
Points: 3039 Visits: 3775
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.



GilaMonster
GilaMonster
SSC Guru
SSC Guru (88K reputation)SSC Guru (88K reputation)SSC Guru (88K reputation)SSC Guru (88K reputation)SSC Guru (88K reputation)SSC Guru (88K reputation)SSC Guru (88K reputation)SSC Guru (88K reputation)

Group: General Forum Members
Points: 88397 Visits: 45283
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>Wink 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

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


Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search