AutoIdentity colums have all starting using much bigger Identity Keys

Question

I have a test database in SQL Server 2012. It has numerous tables with primary key columns of type int where the identity specification is set to yes and the Identity Increment and Seed are both set to one.

For some bizarre reason as I have been testing an application against this database many of these identity columns have suddenly gone from (for example) 9 to 1007. I know that I haven't called routines that would be trying to insert new rows which makes this behaviour all the more strange.

I'm curious as to whether anyone else has experienced this and if so do they know why it was happening?

Because it's a test database I'm not overly worried but I am concerned that new database deployed on an end users machine may exhibit the same quirky behaviour so I'd like to ensure that I have not inadvertently done something silly and set about correcting it if I have.

Thanks for any suggestions you may have.

It is a bug and MS has not come up with a fix for it yet [Read here](https://connect.microsoft.com/SQLServer/feedback/details/739013/alwayson-failover-results-in-reseed-of-identity#details) — M.Ali, Feb 11 '14 at 16:12

score 1 · Accepted Answer · edited May 23 '17 at 12:14

1

This is probably to reseed of the identity upon database restart. You can find more information in this and this answers to related SO questions.

edited May 23 '17 at 12:14

Community

1
1

answered Feb 11 '14 at 16:12

jnovo

5,659
2
38
56

Thank you so much for making highlighting the relevant articles, I'd hadn't realised that this was the case, a very useful lesson learned. – Dom Sinclair Feb 11 '14 at 16:24
Just as an aside does this apply to sql express as well? – Dom Sinclair Feb 11 '14 at 16:25

AutoIdentity colums have all starting using much bigger Identity Keys

1 Answers1