DBCC CHECKDB Consistency Error

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » SQL Server 2005 » Administering » DBCC CHECKDB Consistency Error

15 posts, Page 1 of 2

1 2 »»

DBCC CHECKDB Consistency Error

Rate Topic

Display Mode

Topic Options

Author

Message

MostInterestingMan

Posted Friday, May 14, 2010 8:39 AM

SSC Veteran

Group: General Forum Members
Last Login: Monday, January 30, 2012 2:38 PM
Points: 238, Visits: 575

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

Post #922090

SQLBalls

Posted Friday, May 14, 2010 9:03 AM

Old Hand

Group: General Forum Members
Last Login: Friday, January 13, 2012 11:54 AM
Points: 355, Visits: 522

Have you run an integrity check?

Twitter: @SQLBalls
Blog: http://www.SQLBalls.com

Post #922117

SQLBalls

Posted Friday, May 14, 2010 9:06 AM

Old Hand

Group: General Forum Members
Last Login: Friday, January 13, 2012 11:54 AM
Points: 355, Visits: 522

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

Post #922122

MostInterestingMan

Posted Friday, May 14, 2010 12:43 PM

SSC Veteran

Group: General Forum Members
Last Login: Monday, January 30, 2012 2:38 PM
Points: 238, Visits: 575

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

Post #922256

SQLBalls

Posted Friday, May 14, 2010 12:49 PM

Old Hand

Group: General Forum Members
Last Login: Friday, January 13, 2012 11:54 AM
Points: 355, Visits: 522

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

Post #922261

GilaMonster

Posted Friday, May 14, 2010 1:31 PM

SSC-Dedicated

Group: General Forum Members
Last Login: Today @ 12:01 PM
Points: 30,217, Visits: 23,017

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>) 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
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 #922287

GilaMonster

Posted Friday, May 14, 2010 1:36 PM

SSC-Dedicated

Group: General Forum Members
Last Login: Today @ 12:01 PM
Points: 30,217, Visits: 23,017

What are objects 226099846 and 210099789?

SELECT object_name(226099846)
SELECT object_name(210099789)

Gail Shaw
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 #922289

MostInterestingMan

Posted Sunday, May 16, 2010 8:17 PM

SSC Veteran

Group: General Forum Members
Last Login: Monday, January 30, 2012 2:38 PM
Points: 238, Visits: 575

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

Post #922695

GilaMonster

Posted Monday, May 17, 2010 1:03 AM

SSC-Dedicated

Group: General Forum Members
Last Login: Today @ 12:01 PM
Points: 30,217, Visits: 23,017

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
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 #922729

MostInterestingMan

Posted Monday, May 17, 2010 10:42 AM

SSC Veteran

Group: General Forum Members
Last Login: Monday, January 30, 2012 2:38 PM
Points: 238, Visits: 575

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

Post #923004

« Prev Topic | Next Topic »

15 posts, Page 1 of 2

1 2 »»

Permissions