June 21, 2012 at 2:54 pm
You can actually prevent this behavior on sequence with the NO CACHE option. It's too bad we can't do the same with identity column.
June 29, 2012 at 9:32 am
The gaps in identity columns appear unrelated to the behavior of sequence. As an example a database restored to 2012 from 2008 which has no sequences defined but has identity columns defined with seed and increment (1,1) begins inserting correctly and proceeds as expected, including anticipated gaps as Identity has always behaved. There is no Always On availability set. Upon stopping and restarting the SQL Server and inserting into the same table the Identity Column begins 10,000 higher. So if I had inserted 85 rows and the last insert populated my identity column with 85, after restart the next insert produces an identity of 10,085. Again, no sequences defined, no AlwaysOn set, only Identity Columns on the tables. Does anyone know if there is a mechanism to prevent this?
June 29, 2012 at 9:51 am
In 2012, identities use the same generation method as sequences.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
July 2, 2012 at 2:43 pm
cciappa (6/29/2012)
So if I had inserted 85 rows and the last insert populated my identity column with 85, after restart the next insert produces an identity of 10,085. Again, no sequences defined, no AlwaysOn set, only Identity Columns on the tables. Does anyone know if there is a mechanism to prevent this?
See my earlier post where I explain about using the "scan for on startup procedures" to reseed the identity value as a work around.
I've had feedback from Microsoft saying that this bug has been assigned a priority of "normal".
There is a bug report in Microsoft Connect here: https://connect.microsoft.com/SQLServer/feedback/details/739013/alwayson-failover-results-in-reseed-of-identity%5d#tabs so if you can go here and vote on it, and add yourself to the "can reproduce" list.
I've also added my work around in MS Connect.
Cheers
Leo
Nothing in SQL Server is ever so complicated that with a little work it can't be made more complicated.
Leo
Nothing in life is ever so complicated that with a little work it can't be made more complicated.
July 26, 2012 at 10:26 pm
jeff.mason (6/20/2012)
If the value of the PK is vital, and gaps cannot be supported, I'd look at implementing one of 2012's new features -- a SEQUENCE. You can programmatically control the value that way.
With new SEQUENCE you may also end up in having gaps just like IDENTITY, as it CACHEs the numbers in advance and when you restart you may see gaps.
Better to declare your SEQUENCE with "NO CACHE" option at the end.
August 21, 2012 at 5:23 pm
Leo Miller, I like your work-around. Due to lots of identity cols, have adapted it into a single sp_xxx system stored procedure that reseeds all the identity columns in a given DB. Seems to work ok.
But this only solves problem when it is caused by a service restart. We also experience this problem on availability group failover And I bet this occurs on plain old mirroring failover. So we need a way to trigger this stored procedure call on AG failover too.
I have wasted several hours on this, still do not see a way to do this. I'm guessing if this is possible at all, it lies in an area outside of my expertise (e.g. extended events, or event notifications). Still looking... Does anyone have any ideas how to trigger a stored procedure call upon AG failover?
December 11, 2012 at 7:52 am
If anyone still have this issue, Microsoft finally gave an answer with a solution to this :
December 11, 2012 at 8:54 am
Thanks for posting this. One thing that bothers me though. Microsoft's answer sort of pooh-poohs this whole problem, treats it as though we are merely upset by presence of gaps in identity values, as though it's a question of aesthetics. I don't care about gaps...I worry about possibility of prematurely running out of values for small integer identity columns.
December 31, 2012 at 8:23 am
Is Microsoft phasing out identity columns as we knew them? I don't like having to permanently turn on a trace flag to get the same functionality that we were previously used to; their documentation states that trace flag behavior may not be supported in future releases of SQL Server. Should we start redesigning our databases and programs to use sequences (with No Cache option) in place of identity columns?
February 13, 2014 at 5:55 am
I am using sequence, and the values are jumping as well.
Viewing 10 posts - 16 through 24 (of 24 total)
You must be logged in to reply to this topic. Login to reply