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


DBCC CHECKDB Consistency Error


DBCC CHECKDB Consistency Error

Author
Message
MostInterestingMan
MostInterestingMan
UDP Broadcaster
UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)

Group: General Forum Members
Points: 1465 Visits: 601
I got a consistency error alert this morning for one of my databases. Below is the specific error message:

Msg 8992, Level 16, State 1, Line 1
Check Catalog Msg 3854, State 1: Attribute (parent_object_id=210099789) of row (object_id=226099846) in sys.objects has a matching row (object_id=210099789) in sys.objects (type=S ) that is invalid.

Usually I am use to seeing consistency errors that suggest the type of repair to attempt to recovery the data if possible. Can anyone please advise as to how to handle the above error in respect to attempting to recover the data? I do have backups but it will take couple of days plus a stock pile of paper work to go down that path.

Thanks,

MCTS: BI 2008, MCITP: BI 2008
Stay Thirsty My Friends
SQLBalls
SQLBalls
Ten Centuries
Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)

Group: General Forum Members
Points: 1296 Visits: 630
Have you run an integrity check?

Twitter: @SQLBalls
Blog: http://www.SQLBalls.com
SQLBalls
SQLBalls
Ten Centuries
Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)

Group: General Forum Members
Points: 1296 Visits: 630
Read this, it would indicate a metadate error

http://www.sqlservercentral.com/articles/Corruption/65804/

this article links to this

http://www.sqlskills.com/BLOGS/PAUL/post/TechEd-Demo-Using-the-SQL-2005-Dedicated-Admin-Connection-to-fix-Msg-8992-corrupt-system-tables.aspx


Paul Randal is the man. He wrote DBCC CHECKDB, I would treat whatever he says as gold.

Twitter: @SQLBalls
Blog: http://www.SQLBalls.com
MostInterestingMan
MostInterestingMan
UDP Broadcaster
UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)

Group: General Forum Members
Points: 1465 Visits: 601
Bradley,

Thanks a million for the links - great pointers!!

Yes Paul is "de MAN" on this topic and I should have visited his blog before posting. I will implement some of the suggested solutions in the AM and let you know how things turn out.

Thanks man....

MCTS: BI 2008, MCITP: BI 2008
Stay Thirsty My Friends
SQLBalls
SQLBalls
Ten Centuries
Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)

Group: General Forum Members
Points: 1296 Visits: 630
Glad to hear it!

The team I used to work for had this same issue a couple months after I had left, and I had a drive fall out on a RAID configuration recently and got some corruption errors.

Yeah I dream to have a resume that reads 1/4 as nice as Paul's, and Gail is amazing as well her blog is http://sqlinthewild.co.za/

Twitter: @SQLBalls
Blog: http://www.SQLBalls.com
GilaMonster
GilaMonster
SSC Guru
SSC Guru (233K reputation)SSC Guru (233K reputation)SSC Guru (233K reputation)SSC Guru (233K reputation)SSC Guru (233K reputation)SSC Guru (233K reputation)SSC Guru (233K reputation)SSC Guru (233K reputation)

Group: General Forum Members
Points: 233007 Visits: 46361
That error's not repairable. This is a 2005 server? CheckDB has been succeeding until recently? I ask, cause this is usually the result of someone doing ad-hoc direct updates to the system tables, typically in SQL 2000 prior to a 2005 upgrade.

Can you check the error log, see if there's any mention that the catalog/system tables in that database have been directly updated. Error will appear right at the point SQL brings the DB online.

Can you run the following and post the full and complete output?

DBCC CHECKDB (<Database Name>Wink WITH NO_INFOMSGS, ALL_ERRORMSGS



As for following Paul's blog, I would strongly advise that you do not make any changes to the system tables at the moment. This is not exactly the same error as he talks about there (he's talking about missing references, this is an incorrect reference), and one of the two objects appears to be a system object.

Hacking the system tables without knowing exactly what you're doing can result in worse damage.

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


GilaMonster
GilaMonster
SSC Guru
SSC Guru (233K reputation)SSC Guru (233K reputation)SSC Guru (233K reputation)SSC Guru (233K reputation)SSC Guru (233K reputation)SSC Guru (233K reputation)SSC Guru (233K reputation)SSC Guru (233K reputation)

Group: General Forum Members
Points: 233007 Visits: 46361
What are objects 226099846 and 210099789?

SELECT object_name(226099846)
SELECT object_name(210099789)



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


MostInterestingMan
MostInterestingMan
UDP Broadcaster
UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)

Group: General Forum Members
Points: 1465 Visits: 601
Gail, thanks for taking a look. Below is the information you requested:

DBCC CHECKDB (Deployment_09_24_09) WITH NO_INFOMSGS, ALL_ERRORMSGS

Msg 8992, Level 16, State 1, Line 3
Check Catalog Msg 3854, State 1: Attribute (parent_object_id=210099789) of row (object_id=226099846) in sys.objects has a matching row (object_id=210099789) in sys.objects (type=S ) that is invalid.
CHECKDB found 0 allocation errors and 1 consistency errors not associated with any single object.
CHECKDB found 0 allocation errors and 1 consistency errors in database 'mydatabase'.


SELECT object_name(226099846)
UQ__syskeys__0D7A0286

(1 row(s) affected)

SELECT object_name(210099789)
syskeys

(1 row(s) affected)

MCTS: BI 2008, MCITP: BI 2008
Stay Thirsty My Friends
GilaMonster
GilaMonster
SSC Guru
SSC Guru (233K reputation)SSC Guru (233K reputation)SSC Guru (233K reputation)SSC Guru (233K reputation)SSC Guru (233K reputation)SSC Guru (233K reputation)SSC Guru (233K reputation)SSC Guru (233K reputation)

Group: General Forum Members
Points: 233007 Visits: 46361
This is a SQL 2005 server?

What's the type of syskeys?
SELECT type_desc from sys.objects where name = 'syskeys'

Some other important questions.
Has checkDB been running successfully up until now?
Is this DB an upgraded DB from SQL 2000?
Are there any messages in the error log about direct updates to the system catalog? Would appear right after SQL brings the DB online.

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


MostInterestingMan
MostInterestingMan
UDP Broadcaster
UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)

Group: General Forum Members
Points: 1465 Visits: 601
It is a SQL 2005 Server
The DB is an upgraded DB from SQL 2000
The type for syskeys is SYSTEM_TABLE
My understanding is that CHECKDB was not running in the past
There are no messages about Direct Updates to the system catalog. Will this message show if those updates occurred in the past?

Thanks,

MCTS: BI 2008, MCITP: BI 2008
Stay Thirsty My Friends
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