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:
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:
Microsoft SQL Server 2012 (SP1) - 11.0.3000.0 (X64) Oct 19 2012 13:38:57
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.