SQL 2008 R2 - Check DB Fails - Msg 8992, Level 16, State 1, Line 1 Check Catalog Msg 3853

  • cjackson 22179

    SSC Veteran

    Points: 236

    When I exec the following:

    DBCC CHECKDB (DBNAME) WITH NO_INFOMSGS;

    I am receiving numerous errors similar to the ones that follow.

    Msg 8992, Level 16, State 1, Line 1

    Check Catalog Msg 3853, State 1: Attribute (object_id=12331604) of row (object_id=12331604,parameter_id=1) in sys.parameters does not have a matching row (object_id=12331604) in sys.objects.

    I have determined that there are numerous records in the sys.parameters view which do not have a corresponding record in the sysobjects view.

    select * from sys.parameters param WHERE not exists (select 1 from sys.objects obj where obj.object_id = param.object_id) -. This returns almost 1000 records.

    I am guessing at this point. I believe there were a number of records deleted from system table in a previous version of SQL which was possible but not recommended and the parameters were not deleted.

    Is there a way to clean these up with out rebuilding a database?

  • Gail Shaw

    SSC Guru

    Points: 1004424

    What object has an ID of 12331604?

    And check all the other IDs being reported from CheckDB too.

    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
  • cjackson 22179

    SSC Veteran

    Points: 236

    Thanks for the response - There does not appear to any objectid that matches this number in the sysobjects view. It appears to be an orphaned record in the sys.parameters view.

  • Gail Shaw

    SSC Guru

    Points: 1004424

    Ah, I thought it was the other way around. Ignore them (they're harmless except for causing checkDB to fail) or rebuild the DB (script, export, recreate).

    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
  • cjackson 22179

    SSC Veteran

    Points: 236

    Thank you!

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

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