Failover or Restart Results in Reseed of Identity - FIX

category: [ Data ] tags: [ Adoption Blockers ] [ Old Blog Posts ] [ SQL Server ]
created: 07 Jan 2015 @ 13:17 modified: 02 Aug 2018 @ 14:55

This post has been ported from which I then repeated on

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

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

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.


Gordon Beeming works at Derivco in the sunny city of Durban, South Africa. When he's not hacking away at a keyboard in Visual Studio he'll generally be relaxing with his family or hitting the black top getting in some mileage. He is a Visual Studio ALM Ranger and Visual Studio ALM MVP.

Follow me on Strava


I plan on writing a bunch of online tools and sharing the code for how I made those tools. If you have any feedback you can ping me on Twitter (@GordonBeeming) or mail me [email protected].