locked
DBCC checkdb error Extent in database is marked allocated in the GAM, but no SGAM or IAM has allocated it. RRS feed

  • Question

  • Hi.

    We ran DBCC checkdB command on one of our databases and the below error appeared. it's preventing us from taking database backup while consistency check is enabled.

     
    DBCC results for 'PS_DEV'.
    Msg 8905, Level 16, State 1, Line 3
    Extent (1:131952) in database ID 9 is marked allocated in the GAM, but no SGAM or IAM has allocated it.
    Msg 8905, Level 16, State 1, Line 3
    Extent (1:132032) in database ID 9 is marked allocated in the GAM, but no SGAM or IAM has allocated it.
    Msg 8905, Level 16, State 1, Line 3
    Extent (1:132128) in database ID 9 is marked allocated in the GAM, but no SGAM or IAM has allocated it.
    Msg 8905, Level 16, State 1, Line 3
    Extent (1:132184) in database ID 9 is marked allocated in the GAM, but no SGAM or IAM has allocated it.
    Msg 8905, Level 16, State 1, Line 3
    Extent (1:132232) in database ID 9 is marked allocated in the GAM, but no SGAM or IAM has allocated it.
    CHECKDB found 5 allocation errors and 0 consistency errors not associated with any single object.
    CHECKDB found 5 allocation errors and 0 consistency errors in database 'PS_DEV'.
    repair_allow_data_loss is the minimum repair level for the errors found by DBCC CHECKDB (PS_DEV).
    DBCC execution completed. If DBCC printed error messages, contact your system administrator.

    SQL installed version :  SQL 2005   :  9.0.4262

    Also note that we have checked many documents and forms and the option of restoring to a clean previous backup is not possible because we cant afford loosing any working day.

     


    Abdo Khoury
    Wednesday, November 30, 2011 1:31 PM

Answers

  • Since you have database corruption this is a serious problem.  You will have to do something.

    How often do you run the DBCC CHECKDB?  Is this corruption recent or has it existed for a while?   Do you have a full set of transaction logs from the last good full backup before this corruption problem was found?  

    If so, could you restore a copy of the database from the last good full backup and all of the transaction logs up to the present.  Then test to see if that database also has the corruption problem.

    http://msdn.microsoft.com/en-us/library/aa275767(SQL.80).aspx From SQL Server 2000 suggests hardware problems.  Have you checked for that yet?  A hardware problem could easily add more corruption even after fixing the current problem.

    This article also says, you may have to run DBCC CHECKDB (PS_DEV, REPAIR_ALLOW_DATA_LOSS).  If you do this you will lose some data.  You will then be choosing some unknown set of lost data that would be found in the 5 extents, which is about 300,000 bytes.

    If you wish to examine the pages that may be lost, see: http://blogs.msdn.com/b/sqlserverstorageengine/archive/2006/06/10/625659.aspx

    RLF

     

    • Proposed as answer by Peja Tao Thursday, December 1, 2011 2:26 AM
    • Marked as answer by Peja Tao Tuesday, December 6, 2011 3:40 AM
    Wednesday, November 30, 2011 2:14 PM
  • You are going to face data loss from this if you repair it using REPAIR_ALLOW_DATA_LOSS, which is the only repair option you have, only you have no idea what you lose going that route. 

    Take a backup of the database using CONTINUE_AFTER_ERROR before you do anything, that way you can restore to the current state at a minimum.


    Jonathan Kehayias | Principal Consultant, SQLSkills.com
    SQL Server MVP | Microsoft Certified Master: SQL Server 2008
    Author of Troubleshooting SQL Server: A Guide for Accidental DBAs
    Feel free to contact me through My Blog or Twitter. Become a SQLskills Insider!
    Please click the Mark as Answer button if a post solves your problem!

    • Proposed as answer by Peja Tao Thursday, December 1, 2011 2:26 AM
    • Marked as answer by Peja Tao Tuesday, December 6, 2011 3:41 AM
    Wednesday, November 30, 2011 2:15 PM

All replies

  • Since you have database corruption this is a serious problem.  You will have to do something.

    How often do you run the DBCC CHECKDB?  Is this corruption recent or has it existed for a while?   Do you have a full set of transaction logs from the last good full backup before this corruption problem was found?  

    If so, could you restore a copy of the database from the last good full backup and all of the transaction logs up to the present.  Then test to see if that database also has the corruption problem.

    http://msdn.microsoft.com/en-us/library/aa275767(SQL.80).aspx From SQL Server 2000 suggests hardware problems.  Have you checked for that yet?  A hardware problem could easily add more corruption even after fixing the current problem.

    This article also says, you may have to run DBCC CHECKDB (PS_DEV, REPAIR_ALLOW_DATA_LOSS).  If you do this you will lose some data.  You will then be choosing some unknown set of lost data that would be found in the 5 extents, which is about 300,000 bytes.

    If you wish to examine the pages that may be lost, see: http://blogs.msdn.com/b/sqlserverstorageengine/archive/2006/06/10/625659.aspx

    RLF

     

    • Proposed as answer by Peja Tao Thursday, December 1, 2011 2:26 AM
    • Marked as answer by Peja Tao Tuesday, December 6, 2011 3:40 AM
    Wednesday, November 30, 2011 2:14 PM
  • You are going to face data loss from this if you repair it using REPAIR_ALLOW_DATA_LOSS, which is the only repair option you have, only you have no idea what you lose going that route. 

    Take a backup of the database using CONTINUE_AFTER_ERROR before you do anything, that way you can restore to the current state at a minimum.


    Jonathan Kehayias | Principal Consultant, SQLSkills.com
    SQL Server MVP | Microsoft Certified Master: SQL Server 2008
    Author of Troubleshooting SQL Server: A Guide for Accidental DBAs
    Feel free to contact me through My Blog or Twitter. Become a SQLskills Insider!
    Please click the Mark as Answer button if a post solves your problem!

    • Proposed as answer by Peja Tao Thursday, December 1, 2011 2:26 AM
    • Marked as answer by Peja Tao Tuesday, December 6, 2011 3:41 AM
    Wednesday, November 30, 2011 2:15 PM