Simple 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”.
Links :
- http://connect.microsoft.com/SQLServer/feedback/details/739013/alwayson-failover-results-in-reseed-of-identity
- http://www.sqlservercentral.com/scripts/MS+SQL+2012/93043/