Check Catalog Msg 3854 resulting from dbcc checkdb

Question

Post reply

Check Catalog Msg 3854 resulting from dbcc checkdb

julie taylor

Mr or Mrs. 500

Points: 544
More actions
August 5, 2008 at 3:38 pm

#195337

I'm on SQL2005, and I've let too many days go by, so I can't restore from a backup before this error started occurring:
Msg 8992, Level 16, State 1, Line 1
Check Catalog Msg 3854, State 1: Attribute (parent_object_id=1685633098) of row (object_id=1701633155) in sys.objects has a matching row (object_id=1685633098) 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 'healthdb’
I've read Paul Randal's blog about the 3853 error, where he deletes records to correct the problem.
I logged into the DAC and could not find a sys.objects rec that had parent_object_id= 1685633098 and object_id=1701633155.
If my only choice is to create a new db and extract the data from the corrupted db, do I use the import and export wizard to export the data to a flat file, and then import the data into the new db?
Thanks for any help!

Viewing 15 posts - 1 through 15 (of 21 total)

You must be logged in to reply to this topic. Login to reply

Paul Randal One Orange Chip Points: 29544 More actions · Answer 1

Hi Julie,

Replying here rather than to the email you sent - so next time someone has this there's something on Google for them 🙂

The error is saying that an object (e.g. a constraint) has a parent that has an invalid object type (in this case, a system table).

What are the two objects involved (name, type)? You may be able to get away with dropping one of them rather than having to hack the system tables or export/import.

Thanks

Paul Randal
CEO, SQLskills.com: Check out SQLskills online training!
Blog:www.SQLskills.com/blogs/paul Twitter: @PaulRandal
SQL MVP, Microsoft RD, Contributing Editor of TechNet Magazine
Author of DBCC CHECKDB/repair (and other Storage Engine) code of SQL Server 2005

julie taylor Mr or Mrs. 500 Points: 544 More actions · Answer 2

In sys.objects, the name is UQ__syskeys__656CDC83 and the type is UQ.

Thanks for your help Paul!

Paul Randal One Orange Chip Points: 29544 More actions · Answer 3

What about the other object ID that was referenced? Can you tell me the two names, and which object IDs they are? Thanks

Paul Randal
CEO, SQLskills.com: Check out SQLskills online training!
Blog:www.SQLskills.com/blogs/paul Twitter: @PaulRandal
SQL MVP, Microsoft RD, Contributing Editor of TechNet Magazine
Author of DBCC CHECKDB/repair (and other Storage Engine) code of SQL Server 2005

julie taylor Mr or Mrs. 500 Points: 544 More actions · Answer 4

In the 1st post when I said I couldn't find the rec in sys.objects, it's because I was using the master db. When I changed it to use the corrupted db, I found the record.

The other object's name is syskeys, and the type is S.

Lori B Ten Centuries Points: 1149 More actions · Answer 5

Paul or Julie,

I have this exact same error on a client database that I just started managing. Has there been a resolution? If so, what was it?

Thank you for your help.

-- Lori

julie taylor Mr or Mrs. 500 Points: 544 More actions · Answer 6

Lori,

I haven't heard from Paul after I replied to his question, so this is still an issue.

Julie

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 7

Have a look at this entry on Paul's blog. It may help you

http://www.sqlskills.com/blogs/paul/2008/06/12/TechEdDemoUsingTheSQL2005DedicatedAdminConnectionToFixMsg8992CorruptSystemTables.aspx

Just be very careful and make sure you have a backup before you start. It's easy to break stuff if you're fiddling with the system tables.

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

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 8

julie taylor (8/6/2008)
The other object's name is syskeys, and the type is S.

Are you sure about that? The objectIDs look too high for system objects (which is what type S indicates), and syskeys isn't a system table in SQL 2005.

I don't suppose

ALTER TABLE syskeys DROP CONSTRAINT UQ__syskeys__656CDC83

does anything?

Lori: First thing to do is identify what objects are involved. Can you post relevant portion of the output of CheckDB?

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

Lori B Ten Centuries Points: 1149 More actions · Answer 9

I am certain that syskeys is the right table. The database that I have was just recently upgraded from 2000 to 2005.

Lori B Ten Centuries Points: 1149 More actions · Answer 10

Julie, By chance is your database used for an app named something like Health Master?

Gila, Here is my error message: Check Catalog Msg 3854, State 1: Attribute (parent_object_id=1685633098)

of row (object_id=1701633155) in sys.objects has

a matching row (object_id=1685633098) in sys.objects (type=S ) that is invalid

-- Lori

Paul Randal One Orange Chip Points: 29544 More actions · Answer 11

Well, without knowing something about the app that's using the database it's hard to say which object has corrupt metadata - and how the corruption happened. The only way to fix it is manually using the steps in my blog post, as Gail says, but that's pretty dodgy and totally unsupported - proceed at your own risk.

Strange that two people have the same error - either an app error or a SQL bug at work here.

Nothing much else I can add to this - metadata corruptions like this are notoriously hard to track down.

Thanks

Paul Randal
CEO, SQLskills.com: Check out SQLskills online training!
Blog:www.SQLskills.com/blogs/paul Twitter: @PaulRandal
SQL MVP, Microsoft RD, Contributing Editor of TechNet Magazine
Author of DBCC CHECKDB/repair (and other Storage Engine) code of SQL Server 2005

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 12

Methinks badly behaving application.

It won't be the first time I've seen an application that went and fiddled with the system tables on SQL 2000.

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

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 13

One question, was this database upgraded to SQL 2005 from SQL 2000 or from SQL 7?

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

julie taylor Mr or Mrs. 500 Points: 544 More actions · Answer 14

Hey Lori,

The app is Health Office, by vendor HealthMaster.

Julie