Is it possible to recover cached sequence value after a crash?

Question

I was reading SQL Server 2012 documentation on Sequences. It says that cached sequence values are lost in scenarios like sudden power failure etc.

Is there any way to recover lost cached sequence values from logs? DB2 10 has a feature where it recovers the values from the most recent log.

Answer 1

Automatically, I don't think so... The values will be lost by default, but you might be able to use a startup stored procedure and have it run through your sequence objects, get the max value nad restart the sequence with the lowest value...

ALTER SEQUENCE Demo.Generic_SEQUENCE
RESTART WITH 10

You will have to use dynamic SQL to do it programatically:

DECLARE @QueryText nvarchar(100) =
  CONCAT('ALTER SEQUENCE Demo.TransactionExample_SEQUENCE
          RESTART WITH ',(select MAX(TransactionSequence) + 1 FROM Demo.TransactionExample))
EXEC   (@QueryText);

That query procedes the following query to resets all of the values in the table to contiguous values:

;WITH FixNumbers AS(
SELECT TransactionSequence, ROW_NUMBER() OVER (ORDER BY TransactionSequence) as RowNumber
FROM   Demo.TransactionExample)

UPDATE FixNumbers
SET    TransactionSequence = RowNumber
WHERE  TransactionSequence <> RowNumber; --Start where values are different (performance
                                         --purposes only

The best way to not lose values on a crash (well, other than not crashing :) is to not use caching, which if you need contiguous values, is probably the best idea.  Usually when this question is asked it comes down to the person not really understanding sequence/identity values and that you will lose values if an error occurs, a row is rolled back, etc. Using either identity or sequences for a surrogate key should be treated like the number doesn't have a lot of value other than for joins and singleton row pickups...

I will note that a lot of times that when people ask this question they are missing the point of using an object like this.  If you need a contiguous set in all cases, sequence objects have the same

---

Louis