Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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.
share|improve this question
1  
Why isn't there a PK or Unique constraint on this column? With that in place, this reuse will get caught, and you don't have to try to guess where it's coming from, unless your application code just swallows all errors... –  Aaron Bertrand Jun 16 at 21:23
1  
    
Can you show the definition of your sequence? Also can you check the error log to see if any significant events happened overnight (e.g. failover, service restart, memory issues, etc)? –  Aaron Bertrand Jun 16 at 21:47
2  
What is @@VERSION? Also has anything changed about the environment? There is a connect item reporting something similar. The OP there reckons it was associated with 11.0.3000.0 –  Martin Smith Jun 16 at 21:50
2  
Well, CYCLE essentially tells SQL Server you are okay with reusing values. I have absolutely no idea why you're having this issue, and don't know that you'll find out why (how much time do you spend investigating why you got a flat tire, before you just replace it?). I still think your best bet is to have a constraint there to prevent duplicates, and to turn off caching in hopes of preventing reuse. –  Aaron Bertrand Jun 17 at 16:46

1 Answer 1

up vote 7 down vote accepted

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.

share|improve this answer
    
Well, I can't have it backing up. So if NO CACHE will fix it, then that is what I will do. –  Vaccano Jun 17 at 16:46
    
I thought that there may be transactions causing this. But the Sequence page on MSDN says "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." So I discarded my transaction theory. I agree there must be something else going on. –  Vaccano Jun 17 at 16:50
    
Turns out that just setting it with NO CYCLE was enough. (At least it did not happen last night.) Thank you for the help! –  Vaccano Jun 19 at 16:13

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.