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 CHECKDB Consistency Error Expand / Collapse
Author
Message
Posted Friday, May 14, 2010 8:39 AM


SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Wednesday, February 5, 2014 8:31 AM
Points: 243, 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
Post #922090
Posted Friday, May 14, 2010 9:03 AM


Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Thursday, November 6, 2014 4:16 PM
Points: 386, Visits: 627
Have you run an integrity check?

Twitter: @SQLBalls
Blog: http://www.SQLBalls.com
Post #922117
Posted Friday, May 14, 2010 9:06 AM


Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Thursday, November 6, 2014 4:16 PM
Points: 386, Visits: 627
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
Posted Friday, May 14, 2010 12:43 PM


SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Wednesday, February 5, 2014 8:31 AM
Points: 243, 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
Post #922256
Posted Friday, May 14, 2010 12:49 PM


Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Thursday, November 6, 2014 4:16 PM
Points: 386, Visits: 627
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
Posted Friday, May 14, 2010 1:31 PM


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 @ 1:14 PM
Points: 40,615, Visits: 37,081
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
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 #922287
Posted Friday, May 14, 2010 1:36 PM


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 @ 1:14 PM
Points: 40,615, Visits: 37,081
What are objects 226099846 and 210099789?

SELECT object_name(226099846)
SELECT object_name(210099789)




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 #922289
Posted Sunday, May 16, 2010 8:17 PM


SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Wednesday, February 5, 2014 8:31 AM
Points: 243, 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
Post #922695
Posted Monday, May 17, 2010 1:03 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 @ 1:14 PM
Points: 40,615, Visits: 37,081
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 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 #922729
Posted Monday, May 17, 2010 10:42 AM


SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Wednesday, February 5, 2014 8:31 AM
Points: 243, 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
Post #923004
« Prev Topic | Next Topic »

Add to briefcase 12»»

Permissions Expand / Collapse