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

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?

Garvy

Don't expect identity values to be dense. For example, a rollback when inserting a row will cause an identity value to be "consumed" leaving a gap in the numbers. – Celestina

I was aware that (for example inserting a record that fails) the next number in the identity will be used and lost, it was just slightly alarming when its skips over 1000 – Garvy

If you insert 2000 records in a single statement, and the 1000th fails, you will lose 1000 from your sequence. I once lost 100,000 once in a transaction I rolled back. It's either that or block all inserts until the transaction commits. – Olathe

What the hell is this. We started experiencing the same and it is screwing up with the way manage our data. – Instancy

While it is very helpful to know this is how Sql Server 2012 now works, it is a real problem. The proposed solutions won't work for me as I do not have access to the hosted Sql Server Express server. I also cannot simply use a Sequence Generator as I need consecutive numbers - say for Invoices where a jump of 1,000 is unacceptable or incrementing numbers across several tables won't work. Plus, I may add rows from a Microsoft access front end, so I can't just update the INSERT statement to handle this. I'm considering a non primary key updated in an After Insert trigger. Any better ideas? – Leonorleonora

Answer 1

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:

1. 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.
2. use a sequence generator with the NO CACHE setting (http://msdn.microsoft.com/en-us/library/ff878091.aspx)

Edsel

it's all perfectly normal until you run out of ints – Scammon

I have a 'traditional' identity column with an increment set to 42. The column value incremented by 41706 (eek!) which just happens to be 993*42 – Scammon

@Scammon how often do you restart your sql server? I have been running several instances for over a year now, not a single issue with sequences. – Edsel

we never restart the server and the sequence jumped!! – Edris

With "features" like this, who needs bugs? – Gallenz

Using Sequence comes with its own problems: "General Remarks Sequence numbers are generated outside the scope of the current transaction. They are consumed whether the transaction using the sequence number is committed or rolled back." learn.microsoft.com/en-us/sql/t-sql/statements/… – Mattison

In the link for explanation is not explanation. :( – Phenomenal

this article here explains how to solve it for sql server2014 but I think it is the same problem – Nonary

Answer 2

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

Simmonds

Answer 3

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

Leonorleonora