Identity increment is jumping in SQL Server database

I know my answer might be late to the party. But i have solved in another way by adding a start up stored procedure in SQL Server 2012.

Create a following stored procedure in master DB.

USE [master]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

ALTER PROCEDURE [dbo].[ResetTableNameIdentityAfterRestart]
AS
BEGIN

begin TRAN
    declare @id int = 0
    SELECT @id =  MAX(id) FROM [DatabaseName].dbo.[TableName]
    --print @id
    DBCC CHECKIDENT ('[DatabaseName].dbo.[TableName]', reseed, @id)
Commit

END

Then add it in to Start up by using following syntax.

EXEC sp_procoption 'ResetOrderIdentityAfterRestart', 'startup', 'on';

This is a good idea if you have few tables. but if you have to do for many tables, this method still works but not a good idea.

From SQL Server 2017+ you could use ALTER DATABASE SCOPED CONFIGURATION:

IDENTITY_CACHE = { ON | OFF }

Enables or disables identity cache at the database level. The default is ON. Identity caching is used to improve INSERT performance on tables with Identity columns. To avoid gaps in the values of the Identity column in cases where the server restarts unexpectedly or fails over to a secondary server, disable the IDENTITY_CACHE option. This option is similar to the existing SQL Server Trace Flag 272, except that it can be set at the database level rather than only at the server level.

(...)

G. Set IDENTITY_CACHE

This example disables the identity cache.

ALTER DATABASE SCOPED CONFIGURATION SET IDENTITY_CACHE=OFF ;

You are encountering this behaviour due to a performance improvement since SQL Server 2012.

It now by default uses a cache size of 1,000 when allocating IDENTITY values for an int column and restarting the service can "lose" unused values (The cache size is 10,000 for bigint/numeric).

This is mentioned in the documentation

SQL Server might cache identity values for performance reasons and some of the assigned values can be lost during a database failure or server restart. This can result in gaps in the identity value upon insert. If gaps are not acceptable then the application should use its own mechanism to generate key values. Using a sequence generator with the NOCACHE option can limit the gaps to transactions that are never committed.

From the data you have shown it looks like this happened after the data entry for 22 December then when it restarted SQL Server reserved the values 1206306 - 1207305. After data entry for 24 - 25 December was done another restart and SQL Server reserved the next range 1207306 - 1208305 visible in the entries for the 28th.

Unless you are restarting the service with unusual frequency any "lost" values are unlikely to make any significant dent in the range of values allowed by the datatype so the best policy is not to worry about it.

If this is for some reason a real issue for you some possible workarounds are...

1. You can use a SEQUENCE instead of an identity column and define a smaller cache size for example and use NEXT VALUE FOR in a column default.
2. Or apply trace flag 272 which makes the IDENTITY allocation logged as in versions up to 2008 R2. This applies globally to all databases.
3. Or, for recent versions, execute ALTER DATABASE SCOPED CONFIGURATION SET IDENTITY_CACHE = OFF to disable the identity caching for a specific database.

You should be aware none of these workarounds assure no gaps. This has never been guaranteed by IDENTITY as it would only be possible by serializing inserts to the table. If you need a gapless column you will need to use a different solution than either IDENTITY or SEQUENCE

This problems occurs after restarting the SQL Server.

The solution is:

• Run SQL Server Configuration Manager.

• Select SQL Server Services.

  

• Right-click SQL Server and select Properties.

• In the opening window under Startup Parameters, type -T272 and click Add, then press Apply button and restart.