Failover or Restart Results in Reseed of Identity – FIX

Gordon Beeming

0/5 (0 vote)

May 14, 2013

CPOL

6771

Simply run the script and then every time SQL is restarted, all your Identity columns will be reset to the latest available seed.

This script is a fix for the issue logged here. Hope it helps others like it’s helping me.

USE master; 
GO
CREATE PROCEDURE sp_FixSeeds2012
AS
BEGIN

    --foreach database
    DECLARE @DatabaseName varchar(255)

    DECLARE DatabasesCursor CURSOR READ_ONLY
    FOR
        SELECT name
        FROM sys.databases
        where name not in ('master','tempdb','model','msdb') and 
        sys.databases.state_desc = 'online'

    OPEN DatabasesCursor

    FETCH NEXT FROM DatabasesCursor
    INTO @DatabaseName

    WHILE @@FETCH_STATUS = 0
    BEGIN

        EXEC ('USE '+@DatabaseName + '

        --foreach identity column
        DECLARE @tableName varchar(255)
        DECLARE @columnName varchar(255)
        DECLARE @schemaName varchar(255)

        DECLARE IdentityColumnCursor CURSOR READ_ONLY
        FOR

            select TABLE_NAME , COLUMN_NAME, TABLE_SCHEMA 
            from INFORMATION_SCHEMA.COLUMNS 
            where COLUMNPROPERTY(object_id(TABLE_NAME), COLUMN_NAME,
                                 ''IsIdentity'') = 1 

        OPEN IdentityColumnCursor

        FETCH NEXT FROM IdentityColumnCursor
        INTO @tableName, @columnName, @schemaName

        WHILE @@FETCH_STATUS = 0
        BEGIN

            print ''['+@DatabaseName+'].[''+@tableName+''].[''+
                            @schemaName+''].[''+@columnName+'']'' 
            EXEC (''declare @MAX int = 0
                    select @MAX = max(''+@columnName+'') 
                    from ['+@DatabaseName+'].[''+@schemaName+''].[''+@tableName+'']
                    if (@MAX IS NULL)
                    BEGIN
                        SET @MAX = 0
                    END
                    DBCC CHECKIDENT(['+@DatabaseName+'.''+
                                    @schemaName+''.''+@tableName+''],RESEED,@MAX)'')

            FETCH NEXT FROM IdentityColumnCursor
            INTO @tableName, @columnName, @schemaName

        END

        CLOSE IdentityColumnCursor
        DEALLOCATE IdentityColumnCursor')

        FETCH NEXT FROM DatabasesCursor
        INTO @DatabaseName

    END

    CLOSE DatabasesCursor
    DEALLOCATE DatabasesCursor
END
GO

EXEC sp_configure 'show advanced options', 1 ;
GO
RECONFIGURE
GO
EXEC sp_configure 'scan for startup procs', 1 ;
GO
RECONFIGURE
GO

EXEC sp_procoption @ProcName = 'sp_FixSeeds2012' 
    , @OptionName = 'startup' 
    , @OptionValue = 'true' 
GO

Hope this helps someone that requires this new feature to be “turned off”.

Failover or Restart Results in Reseed of Identity – FIX

Links