MS-SQL Auto Increment Identity Column Value Jump 1000

Long time ago I noticed a bug in one of my Databases, which I discovered later it was in relation with one of Microsoft's features :) :) :). That bug/feature was not stable and occurs in lower rates "lower than 1% of the time". In my case it happened three times along 8 months. Recently, I caught it again on one of my servers.

The bug is all about the Identity field of any table. Where ID sequence misses its order in an unexpected way. So, instead of counting (1, 2, 3, 4 .. etc) it counts (1, 2, 1001, 1002, 1003, .. etc) or (1, 2, 10001, 10002, 10003, .. etc). Sometimes, SQL jumps the sequence of normal counting in the ID field by 1000 or 10000 steps at once.

That makes too much trouble for my Database. Every time occurs, I manually fix the difference and returns sequence to its normality. But, today, and after some investigation I found the reason and the solution.

The Reason: 

Unexpected SQL-Server restarting causes the cache-technique done for the identity to be mis-confused and then SQL rests it to some new steps ahead, in case of "int" it steps 1000 ahead and in case of "bigint" it steps 10000 ahead.

The solution:

I found four solutions. You can select one of them according to your case:

1- Use [SEQUENCE] object: click here

Which means too much change in your application-code and architecture of the data-provider-layer if you have any.

2- Change server trace flag [-t272]: click here

It is a server wide option that is used to disable any cache of any identity-field in any table of any database in the server. That means other databases may be affected if you have multiple.

3- Use [IDENTITY_CAHCE]: click here

It is Only applied for "SQL-2017 (14.x)", and it is used to disable caching technique for specific databases only.

4- Use [DBCC CHECKIDENT]: click here

Create a stored procedure in master database and add it to the starting chain using [sp_procoption], this master wide [STORED PROCEDURE] should be used to reset [IDENTITY] manually for specific tables in specific database, so, every time SQL-Server is restarted it starts and reset Identity using [DBCC CHECKIDENT].