Thursday, August 29, 2013

Extent (Error_ID) in database ID (DB_ID) is marked allocated in the GAM, but no SGAM or IAM has allocated it

Problem: I am getting a database error while checking database with DBCC CHECKDB command. The error message:
Extent (Error_ID) in database ID (DB_ID) is marked allocated in the GAM, but no SGAM or IAM has allocated it.

Solution: To resolve this error first try these sql commands:

    sp_dboption AMMS, single, true
    DBCC CHECKDB (AMMS, REPAIR_REBUILD)
    sp_dboption AMMS, single, false

This will  repair the error with no data loss.

In case of failure of above statements please try with following commands:

    exec sp_dboption AMMS, single, true
    begin try
    DBCC CHECKDB (AMMS, repair_allow_data_loss)
    end try
    begin catch
    DBCC CHECKDB (AMMS, repair_allow_data_loss)
    end catch
    exec sp_dboption AMMS, single, false

There may occur data loss with this statement.

This problem generally occurs when there is some hardware errors. Run hardware diagnostics and correct any problems. Fix any hardware related problems. It might find it beneficial to switch to a completely new hardware system.

How to find out database error?

Problem: How can I find out errors in SQL Server database?

Solution: While running database for a long time on live, it may occur some error in database. To find out database error DBCC CHECKDB command is commonly used. DBCC CHECKDB find out database memory allocation and structural integrity of all object in the specified database. DBCC CHECKDB performs a physical consistency check. DBCC CHECKDB is the safest repair statement because it identifies and repairs the most of the possible errors.

Lets See the syntax of DBCC CHECKDB command:
DBCC CHECKDB
    ( 'database_name'
         [ , NOINDEX  | { REPAIR_ALLOW_DATA_LOSS  |  REPAIR_FAST |  REPAIR_REBUILD } ]
    )    [ WITH { [ ALL_ERRORMSGS ]
                    [ , [ NO_INFOMSGS ] ]
                    [ , [ TABLOCK ] ]
                    [ , [ ESTIMATEONLY ] ]
                    [ , [ PHYSICAL_ONLY ] ]
                    }
        ]

Lets see some important arguments:

'database_name': Name of the database you want to check for memory allocation and structural integrity errors.
NOINDEX: Specifies that non-clustered indexes should not be checked.

REPAIR_FAST: Performs minor, quick repair actions. No risk of data loss.
REPAIR_REBUILD: Performs all repairs done by REPAIR_FAST and repair actions like rebuilding indexes. No risk of data loss.
REPAIR_ALLOW_DATA_LOSS: This performs all repair actions done by REPAIR_REBUILD and includes allocation and reallocation of rows and pages for correcting allocation errors, structural row or page errors, and deletion of corrupted text objects.

Note: Database requires to be in single user mode while specified these arguments.

Example of commands:
1. Check current database errors
    DBCC CHECKDB

2. Check specified database without non-clustered indexes:
    DBCC CHECKDB ('myDb', NOINDEX)


3. Check database errors with REPAIR_REBUILD specified:
    sp_dboption AMMS, single, true
    DBCC CHECKDB (AMMS, REPAIR_REBUILD)
    sp_dboption AMMS, single, false

4. Check database errors with REPAIR_ALLOW_DATA_LOSS specified:
    exec sp_dboption AMMS, single, true
    begin try
    DBCC CHECKDB (AMMS, repair_allow_data_loss)
    end try
    begin catch
    DBCC CHECKDB (AMMS, repair_allow_data_loss)
    end catch
    exec sp_dboption AMMS, single, false