none
How do I fix Damaged allocation pages RRS feed

  • Question

  • I get this result from dbcc checktable


    DBCC results for 'RELATED_PARTY'.
    Msg 8946, Level 16, State 3, Line 1
    Table error: Allocation page (3:443009) has invalid IAM_PAGE page header values. Type is 1. Check type, alloc unit ID and page ID on the page.

    How do I fix this corruption?
    Monday, September 10, 2012 2:21 PM

Answers

  • You seem to have a seriously corrupted database. I suggest you restore from your most recent clean backup and analyze why that corruption occurred in the first place.

    Tibor Karaszi, SQL Server MVP | web | blog


    Friday, September 14, 2012 6:49 AM
    Moderator

All replies

  • Have you got a valid backup..??if so..

    restore the very latest backup into a temporary db

    put db that giving dbcc error into single user mode

    rename RELATED_PARTY to some thing else

    Create the structure of RELATED_PARTY from the restored db

    move data into the new RELATED_PARTY table from the  temporary db

    run checkdb

    put the db back to multi user mode

    vt


    Please mark answered if I've answered your question and vote for it as helpful to help other user's find a solution quicker

    Monday, September 10, 2012 2:44 PM
  • Hello,

    You can use the DBCC CHECKTABLE or CHECKDB with the REPAIR_.. options to fix it; but take a backup before you run such a command.


    Olaf Helper
    Blog Xing

    Monday, September 10, 2012 4:41 PM
    Moderator
  • This worked great, thanks
    Tuesday, September 11, 2012 6:25 PM
  • Running the checkdb with repair was going to be plan B because of the data loss involved with the repair option. 
    Tuesday, September 11, 2012 6:27 PM
  • Here is the latest checkdb results so there is still some work to do. Any thoughts?

    Msg 8905, Level 16, State 1, Line 1 Extent (3:2060072) in database ID 5 is marked allocated in the GAM, but no SGAM or IAM has allocated it. Msg 8905, Level 16, State 1, Line 1 Extent (3:2061480) in database ID 5 is marked allocated in the GAM, but no SGAM or IAM has allocated it. Msg 8905, Level 16, State 1, Line 1 Extent (3:2062544) in database ID 5 is marked allocated in the GAM, but no SGAM or IAM has allocated it. Msg 8905, Level 16, State 1, Line 1 Extent (3:2062896) in database ID 5 is marked allocated in the GAM, but no SGAM or IAM has allocated it. Msg 8905, Level 16, State 1, Line 1 Extent (3:2063952) in database ID 5 is marked allocated in the GAM, but no SGAM or IAM has allocated it. Msg 8905, Level 16, State 1, Line 1 Extent (3:2065016) in database ID 5 is marked allocated in the GAM, but no SGAM or IAM has allocated it. Msg 2575, Level 16, State 1, Line 1 The Index Allocation Map (IAM) page (3:443009) is pointed to by the next pointer of IAM page (3:46916) in object ID 0, index ID -1, partition ID 0, alloc unit ID 72057617638096896 (type Unknown), but it was not detected in the scan. Msg 2576, Level 16, State 1, Line 1 The Index Allocation Map (IAM) page (3:443009) is pointed to by the previous pointer of IAM page (3:56127) in object ID 0, index ID -1, partition ID 0, alloc unit ID 72057617638096896 (type Unknown), but it was not detected in the scan. CHECKDB found 8 allocation errors and 0 consistency errors not associated with any single object. Msg 8910, Level 16, State 1, Line 1 Page (3:441883) in database ID 5 is allocated to both object ID 46935539, index ID 1, partition ID 72057615824584704, alloc unit ID 72057617649303552 (type In-row data), and object ID 1548844880, index ID 1, partition ID 72057615819341824, alloc unit ID 72057617643536384 (type In-row data). CHECKDB found 1 allocation errors and 0 consistency errors in table 'RPT_Mrln_Progress_Pymt_Detail' (object ID 46935539). Msg 8948, Level 16, State 1, Line 1 Database error: Page (3:442994) is marked with the wrong type in PFS page (3:436752). PFS status 0x0 expected 0x60. CHECKDB found 1 allocation errors and 0 consistency errors in table 'syssubscriptions' (object ID 603461524). Msg 8906, Level 16, State 1, Line 1 Page (3:435329) in database ID 5 is allocated in the SGAM (3:3) and PFS (3:428664), but was not allocated in any IAM. PFS flags 'MIXED_EXT ALLOCATED 0_PCT_FULL'. Msg 2575, Level 16, State 1, Line 1 The Index Allocation Map (IAM) page (3:435448) is pointed to by the next pointer of IAM page (0:0) in object ID 987202617, index ID 1, partition ID 72057615804334080, alloc unit ID 72057617626955776 (type In-row data), but it was not detected in the scan. CHECKDB found 2 allocation errors and 0 consistency errors in table 'ENTITY_MATCH_CRITERIA_corrupt' (object ID 987202617). Msg 8948, Level 16, State 1, Line 1 Database error: Page (3:443004) is marked with the wrong type in PFS page (3:436752). PFS status 0x0 expected 0x60. CHECKDB found 1 allocation errors and 0 consistency errors in table 'ALERT_HISTORY' (object ID 1380304077). CHECKDB found 13 allocation errors and 0 consistency errors in database 'Marlin'. repair_allow_data_loss is the minimum repair level for the errors found by DBCC CHECKDB (Marlin).


    Tuesday, September 11, 2012 6:29 PM
  • >CHECKDB found 1 allocation errors and 0 consistency errors in table 'RPT_Mrln_Progress_Pymt_Detail' (object ID 46935539).

    Is this a reporting table which is created automatically? If yes, just drop it. Similarly for indexes.


    Kalman Toth SQL SERVER 2012 & BI TRAINING
    New Book: Beginner Database Design & SQL Programming Using Microsoft SQL Server 2012

    Tuesday, September 11, 2012 7:06 PM
    Moderator
  • Yes it is a reporting table.

    Can you tell me what does this message means?

    Msg 8905, Level 16, State 1, Line 1 Extent (3:2060072) in database ID 5 is marked allocated in the GAM, but no SGAM or IAM has allocated it.

    Tuesday, September 11, 2012 7:20 PM
  • Hello,

    The error is saying that the extent is marked in the GAM (global allocation map page as allocated) as allocated but not SGAM (shared GAM, used to show which extents are mixed extents) or IAM (index allocated map, used to show which extents belong to which objects) has it allocated.

    This means the extent is allocated but it's not allocated to anything which doesn't make sense, thus the error.

    -Sean


    Sean Gallardy, MCC | Blog | Twitter

    Tuesday, September 11, 2012 7:46 PM
    Answerer
  • After some more research I am more concerned about what these three msgs are in the checkdb results. Any help would be welcome.

    Msg 8948, Level 16, State 1, Line 1

    Database error: Page (3:442994) is marked with the wrong type in PFS page (3:436752). PFS status 0x0 expected 0x60.

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

     

    Msg 8906, Level 16, State 1, Line 1

    Page (3:435329) in database ID 5 is allocated in the SGAM (3:3) and PFS (3:428664), but was not allocated in any IAM. PFS flags 'MIXED_EXT ALLOCATED   0_PCT_FULL'.

    Msg 2575, Level 16, State 1, Line 1

    The Index Allocation Map (IAM) page (3:435448) is pointed to by the next pointer of IAM page (0:0) in object ID 987202617, index ID 1, partition ID 72057615804334080, alloc unit ID 72057617626955776 (type In-row data), but it was not detected in the scan.

    Tuesday, September 11, 2012 7:48 PM
  • That is a replication table (syssubscriptions). Are you running replication?

    If not, it can be ignored for the moment. Although for the long term, it is not desirable to operate with checkdb errors.

    Any secondary table (which can be regenerated) or index giving error should be dropped and regenerated.


    Kalman Toth SQL SERVER 2012 & BI TRAINING
    New Book: Beginner Database Design & SQL Programming Using Microsoft SQL Server 2012



    Tuesday, September 11, 2012 8:52 PM
    Moderator
  • You seem to have a seriously corrupted database. I suggest you restore from your most recent clean backup and analyze why that corruption occurred in the first place.

    Tibor Karaszi, SQL Server MVP | web | blog


    Friday, September 14, 2012 6:49 AM
    Moderator