DBCC CHECKDB Consistency Error

  • MostInterestingMan

    SSCertifiable

    Points: 6291

    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

    SSCarpal Tunnel

    Points: 4610

    Have you run an integrity check?

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

  • SQLBalls

    SSCarpal Tunnel

    Points: 4610

    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

    SSCertifiable

    Points: 6291

    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

    SSCarpal Tunnel

    Points: 4610

    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

  • Gail Shaw

    SSC Guru

    Points: 1004424

    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, 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: 1004424

    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

    SSCertifiable

    Points: 6291

    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

  • Gail Shaw

    SSC Guru

    Points: 1004424

    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

    SSCertifiable

    Points: 6291

    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

  • Gail Shaw

    SSC Guru

    Points: 1004424

    Sounds like someone may have messed with the system tables back on SQL 2000 and no one caught it. Odd, syskeys isn't even in any of my SQL 2005 database, let alone as a system table.

    You have two options here.

    1) Recommended. Script all objects, export all data, recreate the database

    2) Hack the 2005 system tables so that they are consistent. I have no idea which tables you'll have to change and what you will have to do to them to get rid of this problem. Note that sys.objects is not a system table. It's a view.

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

    SSChampion

    Points: 12821

    You need to run these two queries which will give you an idea of hat data is corrupt. The records are there, it just looks like a type value has been corrupted. These will return the parent and child records. Post the results and we may be able to help.

    select * from sys.objects where parent_object_id=210099789

    go

    select * from sys.objects where object_id=210099789

    go

    Leo

    Striving to provide a better service.

    Leo
    Nothing in life is ever so complicated that with a little work it can't be made more complicated.

  • MostInterestingMan

    SSCertifiable

    Points: 6291

    Gail, thanks for your time and input. I have a meeting with my boss on Friday and I will present both recommendations - I will keep you posted. Mainwhile I have began researching how to hack system tables in 2005.

    Leo, thanks for your input as well.

    Gail, while I have your attention and since the topic is the same, I ran into more DBCC errors on another environment I'm now in charge of. Just a little history here, my team began providing hosting assistance to one of our dev groups and I am doing an inventory of their manintenance strategy.

    There DBCC CHECKDB errors are below:

    " failed with the following error: "The In-row data RSVD page count for object "tablename", index ID 0, partition ID 68523049811968, alloc unit ID 68523049811968 (type In-row data) is incorrect. Run DBCC UPDATEUSAGE.

    CHECKDB found 0 allocation errors and 1 consistency errors in table 'tablename' (object ID 1045578763).

    CHECKDB found 0 allocation errors and 1 consistency errors in database 'dbname'.". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly.

    Failed:(-1073548784) Executing the query "DBCC CHECKDB WITH NO_INFOMSGS, ALL_ERRORMSGS

    " failed with the following error: "The In-row data RSVD page count for object "tablename", index ID 0, partition ID 98479666036736, alloc unit ID 98479666036736 (type In-row data) is incorrect. Run DBCC UPDATEUSAGE.

    CHECKDB found 0 allocation errors and 1 consistency errors in table 'tablename' (object ID 1502680451).

    CHECKDB found 0 allocation errors and 1 consistency errors in database 'dbname'.". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly.

    Having read your blog is it safe to run the recommended DBCC UPDATEUSAGE command and these issues should be resolved?

    Thanks,

    MCTS: BI 2008, MCITP: BI 2008
    Stay Thirsty My Friends

  • Gail Shaw

    SSC Guru

    Points: 1004424

    cos_ta393 (5/18/2010)


    Gail, thanks for your time and input. I have a meeting with my boss on Friday and I will present both recommendations - I will keep you posted. Mainwhile I have began researching how to hack system tables in 2005.

    Just a word of advice, if I were in your situation, I'd be looking at scripting and exporting, not hacking. Also note that updating the system tables directly will lose you all support from MS for this database (and that info is stored within the DB and logged in the error log whenever the DB is brought online)

    " failed with the following error: "The In-row data RSVD page count for object "tablename", index ID 0, partition ID 68523049811968, alloc unit ID 68523049811968 (type In-row data) is incorrect. Run DBCC UPDATEUSAGE.

    CHECKDB found 0 allocation errors and 1 consistency errors in table 'tablename' (object ID 1045578763).

    CHECKDB found 0 allocation errors and 1 consistency errors in database 'dbname'.". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly.

    Take a look at this article. http://www.sqlservercentral.com/articles/65804/ That particular error is one of the ones detailed in there.

    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

    SSCertifiable

    Points: 6291

    Thanks for your advise - really appreciated.

    And yes I read your article and have successfully resolved those errors.

    Thanks,

    MCTS: BI 2008, MCITP: BI 2008
    Stay Thirsty My Friends

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

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