SQL Server – Identity Column Jump on Restart

identitysql server

I had many research on identity column jump in sql server restart.
I know the reason why this happen as it explained here.

I know the solution how to prevent this using either manually perform CHECKPOINT or –t272 flag (Which should not use).

Why I am here – I am looking for the solution where there no need of
human interaction to RESEED all them back to there valid value using manual script

I have come up with one solution for which I am not sure – is it good or not?

My Solution

When restart performed either manually or server crash, trigger below SQL script using powershell or bat file (configure using TaskScheduler trigger on windows logon).

Script

-- RESEED all column IDENTITY to 0
EXEC sp_MSForEachTable '
 Print ''?''
 IF OBJECTPROPERTY(object_id(''?''), ''TableHasIdentity'') = 1
  DBCC CHECKIDENT (''?'', RESEED, 0)
 else
  Print ''Table does not have an identity value'''

-- RESEED to it's last value
EXEC sp_MSForEachTable '
 Print ''?''
 IF OBJECTPROPERTY(object_id(''?''), ''TableHasIdentity'') = 1
  DBCC CHECKIDENT (''?'', RESEED)
 else
  Print ''Table does not have an identity value'''

Is this good solution? if no then what should be it? any one have other idea?

NOTE:

This gaps are not creating any issue in my application.

Best Answer

If you want something to run on server startup you can create a stored procedure in the master database and mark it as a startup stored procedure as long as the scan for startup procs configuration option is set. No need for powershell or batch files.

I have come up with one solution for which I am not sure - is it good or not?

To determine whether it is a good solution we need to know the problem it is intended to solve. It turns out there is no problem to solve so no this is certainly not a good solution. If it ain't broke don't fix it.

Identity columns have never guaranteed a gapless sequence. If you in fact need one then you would need to roll your own incrementing solution that blocks other transactions until each allocated range has been successfully committed.

The code you posted will reseed all identity columns to 0 and then go through and reseed them all using the maximum value in the identity column? What happens if someone tries to insert rows into a table processed by the first step but not yet processed by the second? They will likely get a duplicate key violation as the insert tries to reuse identity values that have already been allocated to other rows in the table.

Related Question