DBCC Checkdb Problems

  • Hi guys

    Hoping that someone can point me in the right direction on this

    Running DBCC CheckDB against a database, returns the following error:

    Failed:(-1073548784) Executing the query "DBCC CHECKDB(N'XXX') WITH NO_INFOMSGS

    " failed with the following error: "Check Catalog Msg 3853, State 1: Attribute (object_id=528161077) of row (object_id=528161077,column_id=1) in sys.columns does not have a matching row (object_id=528161077) in sys.objects.

    Check Catalog Msg 3853, State 1: Attribute (object_id=528161077) of row (object_id=528161077,column_id=2) in sys.columns does not have a matching row (object_id=528161077) in sys.objects.

    Check Catalog Msg 3853, State 1: Attribute (object_id=528161077) of row (object_id=528161077,column_id=3) in sys.columns does not have a matching row (object_id=528161077) in sys.objects.

    Check Catalog Msg 3853, State 1: Attribute (object_id=528161077) of row (object_id=528161077,column_id=4) in sys.columns does not have a matching row (object_id=528161077) in sys.objects.

    Check Catalog Msg 3853, State 1: Attribute (object_id=528161077) of row (object_id=528161077,column_id=5) in sys.columns does not have a matching row (object_id=528161077) in sys.objects.

    Check Catalog Msg 3853, State 1: Attribute (object_id=528161077) of row (object_id=528161077,column_id=6) in sys.columns does not have a matching row (object_id=528161077) in sys.objects.

    Check Catalog Msg 3853, State 1: Attribute (object_id=528161077) of row (object_id=528161077,column_id=7) in sys.columns does not have a matching row (object_id=528161077) in sys.objects.

    Check Catalog Msg 3853, State 1: Attribute (object_id=528161077) of row (object_id=528161077,column_id=8) in sys.columns does not have a matching row (object_id=528161077) in sys.objects.

    Check Catalog Msg 3853, State 1: Attribute (object_id=528161077) of row (object_id=528161077,column_id=9) in sys.columns does not have a matching row (object_id=528161077) in sys.objects.

    Check Catalog Msg 3853, State 1: Attribute (object_id=528161077) of row (object_id=528161077,column_id=10) in sys.columns does not have a matching row (object_id=528161077) in sys.objects.

    Check Catalog Msg 3853, State 1: Attribute (object_id=528161077) of row (object_id=528161077,column_id=11) in sys.columns does not have a matching row (object_id=528161077) in sys.objects.

    Check Catalog Msg 3853, State 1: Attribute (object_id=528161077) of row (object_id=528161077,column_id=12) in sys.columns does not have a matching row (object_id=528161077) in sys.objects.

    Check Catalog Msg 3853, State 1: Attribute (object_id=528161077) of row (object_id=528161077,column_id=13) in sys.columns does not have a matching row (object_id=528161077) in sys.objects.

    Check Catalog Msg 3853, State 1: Attribute (object_id=528161077) of row (object_id=528161077,column_id=14) in sys.columns does not have a matching row (object_id=528161077) in sys.objects.

    Check Catalog Msg 3853, State 1: Attribute (object_id=528161077) of row (object_id=528161077,column_id=15) in sys.columns does not have a matching row (object_id=528161077) in sys.objects.

    Check Catalog Msg 3853, State 1: Attribute (object_id=528161077) of row (object_id=528161077,column_id=16) in sys.columns does not have a matching row (object_id=528161077) in sys.objects.

    Check Catalog Msg 3853, State 1: Attribute (object_id=528161077) of row (object_id=528161077,column_id=17) in sys.columns does not have a matching row (object_id=528161077) in sys.objects.

    Check Catalog Msg 3853, State 1: Attribute (object_id=528161077) of row (object_id=528161077,column_id=18) in sys.columns does not have a matching row (object_id=528161077) in sys.objects.

    Check Catalog Msg 3853, State 1: Attribute (object_id=528161077) of row (object_id=528161077,column_id=19) in sys.columns does not have a matching row (object_id=528161077) in sys.objects.

    Check Catalog Msg 3853, State 1: Attribute (object_id=528161077) of row (object_id=528161077,column_id=20) in sys.columns does not have a matching row (object_id=528161077) in sys.objects.

    Check Catalog Msg 3853, State 1: Attribute (object_id=528161077) of row (object_id=528161077,column_id=21) in sys.columns does not have a matching row (object_id=528161077) in sys.objects.

    The Index Allocation Map (IAM) page (0:0) is pointed to by the previous pointer of IAM page (1:75945) in object ID 0, index ID -1, partition ID 0, alloc unit ID 316088541052928 (type Unknown), but it was not detected in the scan.

    CHECKDB found 1 allocation errors and 21 consistency errors not associated with any single object.

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

    A bit of background on this, the database in question was recently migrated from a sql 2000 server to sql 2008 R2.My own guess on this is that someone on the 2000 database server altered something on sys.indexes which has caused this.

    Some of the checks we’ve done:

    --identified the names of the columns in question

    select * from syscolumns where id = 528161077

    We could see 1 table that has 20 of the 21 columns, the odd column out was used only in views. We tried dropping these views and the table we identified and ran the dbcc checkdb command again and got the same result

    Ran select * from syscolumns where id = 528161077 again still got 21 results (thought that strange since we had dropped the views where the odd column was used)

    --identified all the tables where this object_id is used in sys.columns

    SELECT distinct c.TABLE_NAME

    FROM INFORMATION_SCHEMA.COLUMNS c

    INNER JOIN

    sys.objects o

    on o.name = c.table_name

    where o.type = 'U'

    and c.column_name in

    (select name from sys.columns

    where object_id = 528161077)

    Query returns 32 tables

    --Ran the following

    dbcc traceon (3604, -1)

    go

    dbcc page ('XXXX',1,75945,3)

    go

    Got the following results

    DBCC execution completed. If DBCC printed error messages, contact your system administrator.

    PAGE: (1:75945)

    BUFFER:

    BUF @0x000000034AFC5200

    bpage = 0x000000034A630000 bhash = 0x0000000000000000 bpageno = (1:75945)

    bdbid = 23 breferences = 3 bcputicks = 0

    bsampleCount = 0 bUse1 = 5041 bstat = 0xc00009

    blog = 0x21212159 bnext = 0x0000000000000000

    PAGE HEADER:

    Page @0x000000034A630000

    m_pageId = (1:75945) m_headerVersion = 1 m_type = 10

    m_typeFlagBits = 0x0 m_level = 0 m_flagBits = 0x0

    m_objId (AllocUnitId.idObj) = 528161077 m_indexId (AllocUnitId.idInd) = 0

    Metadata: AllocUnitId = 34613564342272 Metadata: PartitionId = 0

    Metadata: IndexId = -1 Metadata: ObjectId = 0 m_prevPage = (0:0)

    m_nextPage = (0:0) pminlen = 90 m_slotCnt = 2

    m_freeCnt = 6 m_freeData = 8182 m_reservedCnt = 0

    m_lsn = (441326:400:53) m_xactReserved = 0 m_xdesId = (0:0)

    m_ghostRecCnt = 0 m_tornBits = 1

    Allocation Status

    GAM (1:2) = ALLOCATED SGAM (1:3) = NOT ALLOCATED

    PFS (1:72792) = 0x70 IAM_PG MIXED_EXT ALLOCATED 0_PCT_FULL DIFF (1:6) = NOT CHANGED

    ML (1:7) = NOT MIN_LOGGED

    IAM: Header @0x000000004DACA064 Slot 0, Offset 96

    sequenceNumber = 0 status = 0x0 objectId = 0

    indexId = 0 page_count = 0 start_pg = (1:0)

    IAM: Single Page Allocations @0x000000004DACA08E

    Slot 0 = (1:75944) Slot 1 = (1:75946) Slot 2 = (1:75947)

    Slot 3 = (1:75948) Slot 4 = (1:75949) Slot 5 = (1:75950)

    Slot 6 = (1:75951) Slot 7 = (1:75952)

    IAM: Extent Alloc Status Slot 1 @0x000000004DACA0C2

    (1:0) - (1:75952) = NOT ALLOCATED

    (1:75960) - (1:76736) = ALLOCATED

    (1:76744) - (1:511224) = NOT ALLOCATED

    DBCC execution completed. If DBCC printed error messages, contact your system administrator.

    If anyone can point me in the right direction here it would be greatly appreciated

  • This one's nasty.

    Someone, at some time, made changes directly to the system tables in SQL 2000. SQL 2000 didn't check for that when it ran checkDB, so it went unnoticed. SQL 2008 does, but it's incredibly hard to fix.

    Two options:

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

    2) Hack the system tables in SQL 2008 (no, sys.tables, sys.objects and sys.columns are not the tables). The system tables are completely undocumented, if you make a mistake you could make things worse, and making the changes will leave a permanent record in the database that someone fiddled with the system tables. CSS may refuse to help you if you have problems in the future.

    Given that you also have an IAM error (same kind of cause), I'd go for option 1 if it were my database or a client's database.

    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
  • Cheers Gilla, have read a few blogs and articles about it and it would appear that if all else fails, recreate the database and import the data back across.

    Don't suppose you have any links to threads about doing this in a nice powershell script??

  • mitzyturbo (1/17/2013)


    Cheers Gilla, have read a few blogs and articles about it and it would appear that if all else fails, recreate the database and import the data back across.

    That's what I'd recommend. There's no automated fix for this, checkDB can't repair it. Dropping objects is not going to fix it.

    Don't suppose you have any links to threads about doing this in a nice powershell script??

    What's powershell? 😀

    Sorry, that's not something I use much, so don't know very well.

    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
  • GilaMonster (1/17/2013)


    What's powershell? 😀

    Sorry, that's not something I use much, so don't know very well.

    The specialist is a person who knows everything about something but nothing about anything else.

    -------Bhuvnesh----------
    I work only to learn Sql Server...though my company pays me for getting their stuff done;-)

  • Bhuvnesh (1/17/2013)


    The specialist is a person who knows everything about something but nothing about anything else.

    If you want to be insulting, kindly be so elsewhere.

    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
  • We still have the 2000 instance of this db, is there anyway we can pinpoint the change at that level, identify and migrate the fix?

  • GilaMonster (1/17/2013)


    Bhuvnesh (1/17/2013)


    The specialist is a person who knows everything about something but nothing about anything else.

    If you want to be insulting, kindly be so elsewhere.

    Absolutely NOT gail. i respect you and you know that . actually this was the complement generally my last cmpany's CEO used to give. apology if it hurts you.

    -------Bhuvnesh----------
    I work only to learn Sql Server...though my company pays me for getting their stuff done;-)

  • mitzyturbo (1/17/2013)


    We still have the 2000 instance of this db, is there anyway we can pinpoint the change at that level, identify and migrate the fix?

    Oh yes, absolutely and very easy in SQL 2000. If you can fix there and re-upgrade the DB, that's by far the easiest.

    SELECT * FROM syscolumns WHERE objectid = 528161077

    That should return 21 rows. If so...

    EXEC sp_configure 'allow updates', 1

    RECONFIGURE

    DELETE FROM syscolumns WHERE objectid = 528161077

    GO

    EXEC sp_configure 'allow updates', 0

    RECONFIGURE

    GO

    DBCC CheckCatalog (<database name>)

    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
  • GilaMonster (1/17/2013)


    mitzyturbo (1/17/2013)


    We still have the 2000 instance of this db, is there anyway we can pinpoint the change at that level, identify and migrate the fix?

    Oh yes, absolutely and very easy in SQL 2000. If you can fix there and re-upgrade the DB, that's by far the easiest.

    SELECT * FROM syscolumns WHERE objectid = 528161077

    That should return 21 rows. If so...

    EXEC sp_configure 'allow updates', 1

    RECONFIGURE

    DELETE FROM syscolumns WHERE objectid = 528161077

    GO

    EXEC sp_configure 'allow updates', 1

    RECONFIGURE

    GO

    DBCC CheckCatalog (<database name>)

    I am guessing that the second sp_configure should be to set it back to 0 to prevent updates?

  • Ack. Fixed.

    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
  • Thanks for all the help on this Gail

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

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