Sequence is reusing

Question

I have a sequence that generates tracking numbers for objects in my system. It had been working fine for quite some time.

Last week we noticed that it was starting to re-use values.

What seems to happen is that at different points in the evening, it will rollback to a value it had the previous day. It will then continue generating values from that point.

So for example I could get something like this:

10112
10113
10114
10115
10116
10117
10118
10113
10114
10115
10116
...

There does not seem to be any pattern in when it happens, duration between the first use and second use (as few as 10 min or several hours) or how many are rolled back (as few as 1 and as many as several hundred).

I thought about running a trace (and still may), but I don't think the sequence object is being directly modified. The reason I believe this is that the modify date is several days old and points to a time when we manually bumped the value up to try and eliminate duplicates. (And the issue has occurred several times since then.)

Does anyone have an idea of what could cause a sequence rollback and reuse values each night?

UPDATE: To answer a few questions in the comments:

• @@Version:

  Microsoft SQL Server 2012 (SP1) - 11.0.3000.0 (X64) Oct 19 2012 13:38:57

• Create Script:

  CREATE SEQUENCE [schemaName].[SequenceName] 
    AS [bigint]
    START WITH 410014104
    INCREMENT BY 1
    MINVALUE 410000000
    MAXVALUE 419999999
    CYCLE 
    CACHE 
  GO
  

• I don't have a Unique Constraint (but I plan to put one on). However that will only help me know when I have reused a value. Not what caused the values to reset. I put a job on that would get a new value every 5 min an save it off. The times and value jumps do not follow a pattern.

• I have checked the Event Logs to see if there is an error. The only think that is happening is this: http://support.microsoft.com/kb/2793634 We are applying the fix today. I don't think these are related, but it could be.

Answer 1

First, if you don't want duplicates in this column, state that explicitly.

ALTER TABLE dbo.whatever ADD CONSTRAINT uq_that_column UNIQUE (that_column);

(Or you may want to make that the primary key, or change the clustered index, or what have you...)

In any case, raising an error when you generate a duplicate is far better than just blindly inserting a duplicate that you'll just have to deal with later.

Next, consider that a SEQUENCE is just a number generator, and by default it has a cache of 50 values. Depending on how your transactions are set up, and what other critical events happen on a server, it is possible that SQL Server can "forget" that it generated certain values for you.

There was a bug on Connect (790161) before they eradicated Connect, that talked about these duplicate values, but it seems to have been addressed in this fix (thanks @Andomar):

• KB #3011465 https://support.microsoft.com/en-us/kb/3011465

FIX: Sequence object generates duplicate sequence values when SQL Server 2012 or SQL Server 2014 is under memory pressure

Assume that you create a sequence object that has the CACHE option enabled in Microsoft SQL Server 2012 or SQL Server 2014. When the instance is under memory pressure, and multiple concurrent connections request sequence values from the same sequence object, duplicate sequence values may be generated. In addition, a unique or primary key (PK) violation error occurs when the duplicate sequence value is inserted into a table.

A way to get around this (if that fix doesn't solve your issue, or you are stuck on an ancient and unsupported build without the fix) is to change the sequence to use NO CYCLE and NO CACHE, e.g.:

ALTER SEQUENCE dbo.mysequence NO CYCLE NO CACHE; 

Note that NO CACHE can affect performance and concurrency, but will help eliminate gaps, lost blocks and, who knows, maybe your problem too.