Check Catalog Msg 3854 resulting from dbcc checkdb

  • 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!

  • 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

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

    Thanks for your help Paul!

  • 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

  • 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.

  • 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

  • Lori,

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

    Julie

  • 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
  • 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
  • I am certain that syskeys is the right table. The database that I have was just recently upgraded from 2000 to 2005.

  • 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

  • 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

  • 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
  • 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
  • Hey Lori,

    The app is Health Office, by vendor HealthMaster.

    Julie

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

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