SQL Server 2012 column identity increment jumping from 6 to 1000 on 7th entry

Question

I have a strange scenario in which the auto identity int column in my SQL Server 2012 database is not incrementing properly   Say 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  can not replicate it to find any trends   How is this happening  Is there a way to make it stop

User · Answer

Got the same problem  found the following bug report in SQL Server 2012 If still relevant see conditions that cause the issue - there are some workarounds there as well  didn t try though   Failover or Restart Results in Reseed of Identity

User · Answer

This is all perfectly normal  Microsoft added sequences in SQL Server 2012  finally  i might add and changed the way identity keys are generated  Have a look here for some explanation   If you want to have the old behaviour  you can    use trace flag 272 - this will cause a log record to be generated for each generated identity value  The performance of identity generation may be impacted by turning on this trace flag  use a sequence generator with the NO CACHE setting  http   msdn microsoft com en-us library ff878091 aspx

User · Answer

While trace flag 272 may work for many  it definitely won t work for hosted Sql Server Express installations  So  I created an identity table  and use this through an INSTEAD OF trigger  I m hoping this helps someone else  and or gives others an opportunity to improve my solution  The last line allows returning the last identity column added  Since I typically use this to add a single row  this works to return the identity of a single inserted row   The identity table   CREATE TABLE  dbo   tblsysIdentities    intTableId   int  NOT NULL   intIdentityLast   int  NOT NULL   strTable   varchar  100  NOT NULL   tsConcurrency   timestamp  NULL  CONSTRAINT  PK tblsysIdentities  PRIMARY KEY CLUSTERED         intTableId  ASC  WITH  PAD INDEX   OFF  STATISTICS NORECOMPUTE   OFF  IGNORE DUP KEY   OFF   ALLOW ROW LOCKS   ON  ALLOW PAGE LOCKS   ON  ON  PRIMARY    ON  PRIMARY    and the insert trigger   -- INSERT -- IF OBJECT ID   dbo trgtblsysTrackerMessagesIdentity    TR   IS NOT NULL    DROP TRIGGER dbo trgtblsysTrackerMessagesIdentity  GO CREATE TRIGGER trgtblsysTrackerMessagesIdentity ON dbo tblsysTrackerMessages INSTEAD OF INSERT AS  BEGIN     DECLARE  intTrackerMessageId INT     DECLARE  intRowCount INT      SET  intRowCount    SELECT COUNT    FROM INSERTED       SET  intTrackerMessageId    SELECT intIdentityLast FROM tblsysIdentities WHERE intTableId 1      UPDATE tblsysIdentities SET intIdentityLast    intTrackerMessageId    intRowCount WHERE intTableId 1      INSERT INTO tblsysTrackerMessages        intTrackerMessageId        intTrackerId        strMessage        intTrackerMessageTypeId        datCreated        strCreatedBy       SELECT  intTrackerMessageId   ROW NUMBER   OVER  ORDER BY  datCreated   AS  intTrackerMessageId         intTrackerId       strMessage       intTrackerMessageTypeId       datCreated       strCreatedBy  FROM INSERTED      SELECT TOP 1  intTrackerMessageId    intRowCount FROM INSERTED  END

[sql] SQL Server 2012 column identity increment jumping from 6 to 1000+ on 7th entry

Examples related to sql

Examples related to sql-server

Examples related to sql-server-2012

Examples related to identity