SQL Server Performance

DBCC Checkcatalog Error

Discussion in 'SQL Server 2005 General DBA Questions' started by roopaprp, Apr 20, 2007.

  1. roopaprp New Member

    I have restored a backup on SQL server 2005 My Maintenance Plans fails during the Integrity Check . Based on the error found in the log,I did the DBCC checkcatalog and it is giving the following error

    Msg 3853, Level 16, State 1, Line 1
    Attribute (object_id=1640392913) of row (object_id=1640392913,column_id=1) in sys.columns does not have a matching row (object_id=1640392913) in sys.objects.
    Msg 3853, Level 16, State 1, Line 1
    Attribute (object_id=1640392913) of row (object_id=1640392913,column_id=2) in sys.columns does not have a matching row (object_id=1640392913) in sys.objects.
    Msg 3853, Level 16, State 1, Line 1
    Attribute (object_id=1640392913) of row (object_id=1640392913,column_id=3) in sys.columns does not have a matching row (object_id=1640392913) in sys.objects.
    Msg 3853, Level 16, State 1, Line 1
    Attribute (parent_object_id=1640392913) of row (object_id=1297439696) in sys.objects does not have a matching row (object_id=1640392913) in sys.objects.

    How am i supposed to fix this?

    Thanks

    Roopa
  2. ghemant Moderator

    Did you check the event log? Is their any error for HDD?
    have you tried running dbcc checkdb? against a database?

    Hemantgiri S. Goswami
    MS SQL Server MVP
    -------------------------
    "Humans don't have Caliber to PASS TIME , Time it self Pass or Fail Humans" - by Hemantgiri S. Goswami

    http://hemantgirisgoswami.blogspot.com
  3. roopaprp New Member

    Hey Hemanth,
    I am getting the following error with the DBCC CHECKDB as well. The is no probelm with HDD.


    Msg 8992, Level 16, State 1, Line 1
    Check Catalog Msg 3853, State 1: Attribute (object_id=1640392913) of row (object_id=1640392913,column_id=1) in sys.columns does not have a matching row (object_id=1640392913) in sys.objects.
    Msg 8992, Level 16, State 1, Line 1
    Check Catalog Msg 3853, State 1: Attribute (object_id=1640392913) of row (object_id=1640392913,column_id=2) in sys.columns does not have a matching row (object_id=1640392913) in sys.objects.
    Msg 8992, Level 16, State 1, Line 1
    Check Catalog Msg 3853, State 1: Attribute (object_id=1640392913) of row (object_id=1640392913,column_id=3) in sys.columns does not have a matching row (object_id=1640392913) in sys.objects.
    Msg 8992, Level 16, State 1, Line 1
    Check Catalog Msg 3853, State 1: Attribute (parent_object_id=1640392913) of row (object_id=1297439696) in sys.objects does not have a matching row (object_id=1640392913) in sys.objects.
    Msg 2576, Level 16, State 1, Line 1
    The Index Allocation Map (IAM) page (0:0) is pointed to by the previous pointer of IAM page (1:79969) in object ID 0, index ID -1, partition ID 0, alloc unit ID 107504789946368 (type Unknown), but it was not detected in the scan.

    Thanks

    Roopa
  4. MohammedU New Member

    Seems to row i missing in sys.columns table..
    First figure it out what is object name...
    SELECT OBJECT_NAME(1640392913)
    See you can copy the data to a different table and drop this table...

    OR Try the following..

    DBCC CHECKDB ('DBNAME', REPAIR_FAST) for least data loss...
    If it didn't work then try REPAIR with data loss option.

    MohammedU.
    Moderator
    SQL-Server-Performance.com

    All postings are provided “AS IS” with no warranties for accuracy.
  5. satya Moderator

    I don't think REPAIR_FAST would do any magic, only go with data loss option.
    Best thing is to restore the database from last backup and check for specified table to import on main table.

    Satya SKJ
    Microsoft SQL Server MVP
    Writer, Contributing Editor & Moderator
    http://www.SQL-Server-Performance.Com
    This posting is provided AS IS with no rights for the sake of knowledge sharing. The greatest discovery of my generation is that a human being can alter his life by altering his attitudes of mind.
  6. roopaprp New Member

    As told, I set the database in the single user mode and ran the following commands against it.

    DBCC CHECKDB ('CPQFedprograms', REPAIR_FAST)


    DBCC CHECKDB ('CPQFedprograms', REPAIR_ALLOW_DATA_LOSS)

    but still it is showing me that same errors.

    and the SELECT OBJECT_NAME(1640392913)

    does not return any object name.

    Thanks

    Roopa


  7. MohammedU New Member

    If you see your checkdb output, you have problem with system tables...it clearly say you don't have the matching row in sys.objects where as you do have in sys.columns...

    Try
    select object_name(id) from sys.columns where id = 1640392913

    If you get the object name, try to drop the effected obejcts...

    If it is important database, call MS and see what do they suggest.


    MohammedU.
    Moderator
    SQL-Server-Performance.com

    All postings are provided “AS IS” with no warranties for accuracy.
  8. roopaprp New Member

    I tired this , but still it does not return any object name. It shows " No Column Name"

    Thanks

    Roopa
  9. MohammedU New Member

    Looks like your system table may have corruption.
    As I mentioned before, if it is critical database open a ticke with Microsoft.


    MohammedU.
    Moderator
    SQL-Server-Performance.com

    All postings are provided “AS IS” with no warranties for accuracy.
  10. roopaprp New Member

    Thank you all for all the input. As a last option I am trying to restore a fresh backup again. Hope this works out.

    Thanks

    Roopa
  11. MohammedU New Member

    Good luck...

    MohammedU.
    Moderator
    SQL-Server-Performance.com

    All postings are provided “AS IS” with no warranties for accuracy.
  12. satya Moderator

    Have you checked the hardware for any issues, I have seen similar issues having a hard disk controller failure cause the corruption in table pages.

    Satya SKJ
    Microsoft SQL Server MVP
    Writer, Contributing Editor & Moderator
    http://www.SQL-Server-Performance.Com
    This posting is provided AS IS with no rights for the sake of knowledge sharing. The greatest discovery of my generation is that a human being can alter his life by altering his attitudes of mind.
  13. TP_MSORADEV New Member

  14. satya Moderator

    Have you followed whatever mentiond above?
  15. TP_MSORADEV New Member

  16. tush New Member

    make sure you run the command SELECT OBJECT_NAME(1640392913) on the database you have the problem not the master as i had a similar problem and when i ran on the faulty database it gave the name of the stored procedure causing the problem.
    I dropped the procedure and recreated it .The command dbcc checkdb now works fine with no errors.
  17. TP_MSORADEV New Member

    Thanks, but i cannot drop an object that does not exist!! There is no entry in sysobjects (yes, the database is correct!).
  18. satya Moderator

    As a further test its better to restore the same database on the other machien to see whether it is reporting, in any case better to perform DBCC CHECKDB, UPDATEUSAGE & REINDEX in order to update all off the system objects too.

Share This Page