DBCC CHECKDB - error Msg 8992

  • Hi Gurus,

    In one of my server, while executing DBCC Checkdb for a database, it is throwing the below error. If anybody experienced this error, please share your thoughts, that would be highly appreciated.

    Error details

    Msg 8992

    , Level 16, State 1, Line 1

    Check

    Catalog Msg 3853, State 1:

    Attribute

    (referenced_major_id=982294559,referenced_minor_id=1) of row

    (

    class=0,object_id=1398296041,column_id=0,referenced_major_id=982294559,referenced_minor_id=1)

    in

    sys.sql_dependencies does not have a matching row (object_id=982294559,column_id=1)

    in

    sys.columns.

     

    thanks in advance to all.

     

    warmest

    Jayakumar K

  • This is an open wide error which can point to Users Role/Login, Re-complie of OLD SP, DB_OWNER role is missing when you migrate/transfer Database/tables from SQL 2000. worst come you have to re-create the database from scratch and then manually reload tables. I hope this will be helpfull.

     

    Thanks

  • Yes, but is there a way to fix it?  I'm struggling with 4 of these errors at the moment in a database I recently migrated from SQL2000.  Odd thing is, it passes the consistency check fine on that platform.

    In SQL2000, I'd simply take a peek at sysdepends and blow away the 4 rows it's complaining about.  No can do anymore...

    Msg 8992

    , Level 16, State 1, Line 1

    Check

    Catalog Msg 3853, State 1: Attribute (referenced_major_id=1941581955,referenced_minor_id=27) of row (class=0,object_id=1835153583,column_id=0,referenced_major_id=1941581955,referenced_minor_id=27) in sys.sql_dependencies does not have a matching row (object_id=1941581955,column_id=27) in sys.columns.

    Msg 8992

    , Level 16, State 1, Line 1

    Check

    Catalog Msg 3853, State 1: Attribute (referenced_major_id=1941581955,referenced_minor_id=27) of row (class=0,object_id=1851153640,column_id=0,referenced_major_id=1941581955,referenced_minor_id=27) in sys.sql_dependencies does not have a matching row (object_id=1941581955,column_id=27) in sys.columns.

    Msg 8992

    , Level 16, State 1, Line 1

    Check

    Catalog Msg 3853, State 1: Attribute (referenced_major_id=1941581955,referenced_minor_id=27) of row (class=0,object_id=1867153697,column_id=0,referenced_major_id=1941581955,referenced_minor_id=27) in sys.sql_dependencies does not have a matching row (object_id=1941581955,column_id=27) in sys.columns.

    Msg 8992

    , Level 16, State 1, Line 1

    Check

    Catalog Msg 3853, State 1: Attribute (referenced_major_id=1941581955,referenced_minor_id=27) of row (class=0,object_id=1883153754,column_id=0,referenced_major_id=1941581955,referenced_minor_id=27) in sys.sql_dependencies does not have a matching row (object_id=1941581955,column_id=27) in sys.columns.

    I first did some research:

    select

    * from sys.sql_dependencies where referenced_major_id=1941581955 and referenced_minor_id=27

    Then tried to do cleanup for one particular row:

    delete

    sysdepends where [id]=1851153640 and depid=1941581955 and depnumber=27

    Error!

    Msg 259, Level 16, State 1, Line 1
    Ad hoc updates to system catalogs are not allowed.
    I tried a suggestion I found with a Google search, 'exec sp_configure 'allow updates', 1' followed by 'reconfigure with override', but no luck...
    These DMVs and such in SQL2005 are really starting to agitate me!  Running a DBCC CHECKDB with either REPAIR_REBUILD or REPAIR_ALLOW_DATA_LOSS don't fix the underlying problem.
    Any suggestions?

  • The saga continues...   now the job "succeeds", but clearly doesn't do anything, per the fact that no backups get created and the message in the log: 

    Microsoft(R) Server Maintenance Utility (Unicode) Version 9.0.2153

    Report was generated on "wimkesql04".

    Maintenance Plan: Database Backups

    Duration: 00:00:01

    Status: Warning: One or more tasks failed..

    Details:

    Check Database Integrity Task (wimkesql04)

    Check Database integrity on Local server connection

    Databases: All user databases

    Include indexes

    Task start: 8/31/2006 11:30 PM.

    Task end: 8/31/2006 11:30 PM.

    Failed: (0) Alter failed for Server 'wimkesql04'.

    I don't know what to be more alarmed about - the fact that a job can show a successful finish when that's not the case (very alarming - can I trust SQL Server 2005?) or the fact that it just started working differently from one day to the next.

    Did I mention this is a simple Maintenance Plan, first step does an integrity check, second step does a shrink, and third step does a backup - all User databases.

     
  • Jay.

    In SQL 2005, checkdb is more strict then in SQL 2000.

    Please note that 'exec sp_configure 'allow updates', 1' is not allowed in SQL 2005.

    To move forward, what are objects? Please run below queries.

    select

    object_id, name, type_desc from sys.objects

    where

    object_id in (1941581955 , 1835153583)

    contact me at offline at bmlakhani@yahoo.com

     

  • Another question! was this database restored from SQL 2000 backup?

  • I can't speak for Jay, but I know that was the case for my database that was having the issue.  Is there anything other than setting the Compatibility Level from 8 to 9?

  • Chad,

    DBCC CHECKDB runs DBCC CHECKCATALOG also in SQL 2005.

    As per my understanding there is some reference for columns in stored procedure which are not available.

    Its my assumption. To prove the theory, I need to wait for Jay's response to confirm this

     

    -B

  • Jay,

    Did you ever resolve your Check Catalog Msg 3853 error reported in SQLServerCentral please? I'm having the same problem and would appreciate your advice? Please could you update your post?

  • Hi Adrian,

    Before come to a resolution for that issue I switched from the company in the same month.

    Appologies for the inconvenience caused...

    warmest

    Jay.

  • Thanks Jay.

    I've opened a call with Microsoft and will try to remember to post an update when available.

  • Microsoft used the references in my error message from this problem to identify a related stored procedure that was pointing to columns in table(s) that no longer existed. The stored procedure was recompiled and the problem went away. Checkdb runs successfully now. As refected in prior messages, I think checkdb is more thorough in 2005 and picks up "errors" such as these.

  • In my case, the consistency error affects a view.  I dropped and recreated the view, and then ran DBCC CHECKDB on the database again, and the eorr is now gone.

    So check the object affected and recreate it.

     

  • I think you'll find the first occurring object number relates to a stored procedure. If you recompile that stored procedure, the required system columns are populated after a migration, and th eerror goes away.

  • Hi im having the same problem,

    How can i find out which View or Stored Procedure is incorrect

    My Error:

    Msg 8992, Level 16, State 1, Line 1

    Check Catalog Msg 3853, State 1: Attribute (referenced_major_id=11199140,referenced_minor_id=28) of row (class=0,object_id=27199197,column_id=0,referenced_major_id=11199140,referenced_minor_id=28) in sys.sql_dependencies does not have a matching row (object_id=11199140,column_id=28) in sys.columns.

    Thanks

    George

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

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