Failover or Restart Results in Reseed of Identity - FIX

07 Jan 2015

Adoption Blockers Data Old Blog Posts SQL Server

This post has been ported from http://www.sqlservercentral.com/scripts/MS+SQL+2012/93043/ which I then repeated on https://gbeeming.wordpress.com/2013/02/12/failover-or-restart-results-in-reseed-of-identity-fix/.

There existed an "issue" for us when we moved to SQL 2012 where every time your SQL instance restarted for any reason be it manually or server unexpectedly turned off your identity columns would have jumped numbers on the next record being inserted. It was also raised on the connect site http://connect.microsoft.com/SQLServer/feedback/details/739013/alwayson-failover-results-in-reseed-of-identity.

We found out that this was not actually an issue and was by design for performance to optimize the amount of disk IOs that were required to generate sequence numbers. A post on SQL Server Training describes how to change the cache size http://www.sqlserver-training.com/sequence-breaks-gap-in-numbers-after-restart-sql-server-gap-between-numbers-after-restarting-server/-.

Before seeing that post however I developed another solution that basically reseeded every table on instance startup so the issue didn't exist. To use this solution you can just run the script below on your SQL instance.

 


 

Hope this helps someone as I see the connect issue on it is still active.