0

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.

2

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.

  • That might get him in trouble with auditors, typically it's recommended to turn that off. What has your experience been with it though? – Ali Razeghi Apr 26 '16 at 16:45
  • @AliRazeghi I haven't had much call to use it. Conceptually I don't see why running a startup procedure would be any different from any running any other code that is external but designed to run at server startup though. Permissions should prevent anyone unauthorised from creating procs in master and setting this option. But in any event the rest of my answer indicates that the OP doesn't need to do this anyway. – Martin Smith Apr 26 '16 at 16:48

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.