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
   

How to get list of sql server database objects?

Problem: How to get list of database objects?

 Solution: You can get list of objects in two ways.

1. Getting the list from sys.objects table. Ex:
 
List of tables:

SELECT *
FROM sys.objects where type='U'

List of stored procedures:

SELECT *
FROM sys.objects where type='P'

List of views:

SELECT *
FROM sys.objects where type='V'


2. Getting the list query from object related tables. Ex:


List of tables:


SELECT *
FROM sys.Tables


List of stored procedures:


SELECT *
FROM sys.procedures


List of views:


SELECT *
FROM sys.views

How to remove all data from table and reseed identity column to initial value?

Problem: How to remove all data from table and reseed identity column to initial value?

 Solution: For this purpose best solution is to use truncate sql command when you want to delete all data and reset identity column value to initial value.
Ex. TRUNCATE TABLE tableName

Somebody uses the following command which results same output:

DELETE FROM tableName
DBCC CHECKIDENT (tableName,RESEED, 0)

An alternative to cursor

While working with database we often require to use Cursor. Cursors force the database engine to repeatedly fetch rows. This may slow down your database and locking may occur. As a result cursor are not suggested to use if not extremely necessary.

If there is a primary key on a table, you can usually write a WHILE loop to do the same work without incurring the overhead of a cursor. The following example demonstrate the idea:

Declare @ItemId bigint

Declare @OldItemCode varchar(50)

      SELECT Top 1   @ItemId= ItemId, @OldItemCode=OldItemCode

      FROM         InvItem

      WHERE      (IsStockItem = 'true')

      WHILE @ItemId is not null

      BEGIN

            print 'Item: '+convert(varchar(10),@ItemId)+' - '+convert(varchar(50),@OldItemCode)

            if exists (select ItemId from InvItem where IsStockItem=1 and ItemId>@ItemId)

                  begin

                        SELECT Top 1    @ItemId=ItemId, @OldItemCode=OldItemCode

                        FROM         InvItem

                        WHERE      (IsStockItem = 'true') and ItemId>@ItemId

                  end

                  else

                  begin

                        set @ItemId=null

                  end

      END

How can I install asp.net membership schema in my existing database?

Installing membership related schema to an existing database is very easy task. Asp.net SQL Server Registration Tool(aspnet_regsql.exe) provides a very easy way to achieve the goal. We can run the registration tool from from Visual Studio Command Prompt or we can navigate to aspnet_regsql.exe file location and run the file.

To run from command prompt navigate to:
Start Menu> Microsoft Visual Studio 2010> Visual Studio Tools>Visual Studio Command Prompt(2010)
In the command prompt write aspnet_regsql command and press enter key as the screen shows.

To run from aspnet_regsql.exe  file location navigate to:
%Windows%\Microsoft.NET\Framework\v4.0.30319 and double click on aspnet_regsql.exe to run the tool.

ASP.NET SQL Server Setup Wizard will be appeared as follows:



Press Next button and you will get following screen:


On the next screen you will be prompted to select the database with credentials to which you want to setup membership.


Click Next to confirm your settings and click next again to execute the wizard and finally asp.net membership schema will be created on your database.

If you are interested to know more on asp.net membership you can navigate here:
Examining ASP.NET's Membership, Roles, and Profile

Thanks