Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

gaps in the identity column Expand / Collapse
Author
Message
Posted Thursday, February 13, 2014 7:00 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Tuesday, December 2, 2014 7:42 AM
Points: 43, Visits: 164


The identity int column in my sql2012 db is not incrementing properly.



I have a table which uses an int auto identity as a primary key it is sporadically skipping increments, for example:

1, 2, 3, 4, 5, 1004, 1005

This is happening on a random number of tables at very random times

any suggestions on how to stop this

Thanks
Post #1541170
Posted Thursday, February 13, 2014 7:14 AM
SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Yesterday @ 9:49 AM
Points: 858, Visits: 2,503
Hi,
There's a connect item about this somewhere (I'll try and find it). Basically, it's by design. There's a trace flag in the connect item I've linked to (just seen it)
Post #1541180
Posted Thursday, February 13, 2014 7:19 AM
SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Yesterday @ 9:49 AM
Points: 858, Visits: 2,503
here:
http://connect.microsoft.com/SQLServer/feedback/details/743300/identity-column-jumps-by-seed-value

There's another connect item with a better explanation of why it is by design - I recollect it's to do with performance... If I find it, i'll post that link too

Edit - new link https://connect.microsoft.com/SQLServer/feedback/details/739013/failover-or-restart-results-in-reseed-of-identity



Posted by Microsoft on 5/6/2013 at 3:53 PM
Hello all,

I am the dev owning the identity feature.
To boost the preformance for high end machines, we introduce preallocation for identity value in 2012.
And this feature can be disabled by using TF 272 (then you will get the behaviour from 2008R2).

The identity properties are stored separately in metadata.
If a value is used in identity and increment is called, then the new seed value will be set.
No operation, including Rollback, Failover, ..... can change the seed value except DBCC reseed.
Failover applies for the table object, but no the identity object.
So for failover, you can call checkpoint before manual failover, but you may see gap for unplanned cases.
If gap is a concern, then I suggest you to use TF 272.

For control manager shutdown, we have a fix for next verion (with another TF). This fix will take care of most control manager shutdown cases.

Thanks,
Bryan

Post #1541182
Posted Thursday, February 13, 2014 7:50 AM


SSCarpal Tunnel

SSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal Tunnel

Group: General Forum Members
Last Login: Today @ 7:31 AM
Points: 4,472, Visits: 6,402
Remember too that there is no guarantee that you won't have gaps in identity values for other reasons, such as transaction rollbacks. If you need no gaps, you need another solution (which is very complex to manage and deal with).

Best,

Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru at GMail
Post #1541204
Posted Thursday, February 13, 2014 9:11 AM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Monday, August 18, 2014 3:04 AM
Points: 1,331, Visits: 15,269
TheSQLGuru (2/13/2014)
such as transaction rollbacks.


Beat me to it.

If a transaction fails and rolls back, the identity is "lost". It isn't de-incremented as part of the roll back
Post #1541277
Posted Monday, February 17, 2014 4:08 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Tuesday, December 2, 2014 7:42 AM
Points: 43, Visits: 164
thnx everyone for your help...
Post #1542033
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse