I was surprised to find out that overnight, a unique identifier had jumped from 6 to 1012 and upon investigation, it looks like SQL Server, when restarted, messes up the seed of the unique identifier to a new value (this is why you should never use ints for primary keys!)
Don’t expect identity values to be dense. For example, a rollback when inserting a row will cause an identity value to be “consumed” leaving a gap in the numbers.
I wrote a small SP which can be used to re-seeed the identity of a table in a all DBs on a server:
USE [master] GO SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO ALTER PROCEDURE [dbo].[ResetOrderIdentityAfterRestart] AS BEGIN begin TRAN SET NOCOUNT ON; DECLARE @db_name varchar(280), @SQL nvarchar(500); PRINT '-------- DB Reseet Report --------'; DECLARE db_name_cursor CURSOR FOR select DatabaseName + '.dbo.table' from tblClients WHERE ACTIVE = 1 OPEN db_name_cursor FETCH NEXT FROM db_name_cursor INTO @db_name WHILE @@FETCH_STATUS = 0 BEGIN PRINT ' ' print '----- Resetting count: ' + @db_name -- Declare an inner cursor based -- on vendor_id from the outer cursor. SET @SQL = ' declare @id int = 0 SELECT @id = MAX(ID) FROM ' + @db_name + ' DBCC CHECKIDENT (''' + @db_name + ''', reseed, @id) ' exec sp_executeSQL @SQL FETCH NEXT FROM db_name_cursor INTO @db_name END CLOSE db_name_cursor; DEALLOCATE db_name_cursor; Commit END
Then add it in to Start up by using following syntax.
EXEC sp_procoption 'ResetOrderIdentityAfterRestart', 'startup', 'on';