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 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.

ABOUT ME

Gordon Beeming works at Nologo Studios in the sunny city of Durban, South Africa. He is the Lead for the Data and Services Team and has a strong focus on Developer Efficiencies and R&D. 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