SQL Server 2012 – Troubleshooting Sequence Reuse Issues

sequencesql serversql-server-2012

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.

Best Answer

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. Sorry but I do not know exactly what criteria factor into reproducing this bug. The way to get around this (until the bug is solved/explained) 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.

You might also want to verify that you're on the most recent service pack and CU. At this point I recommend SP1 and CU10 with 3437; SP2 is out but there is still a critical issue there with online rebuilds that may affect you.

Related Question