2

So in the middle of basic coding and testing we saw a huge non-patterned jump in Identity values for multiple tables. We are unaware of any server blips or attempted bulk operations, but DBAs are looking into logs.

The gaps are not the typical 1,000 or 10,000 seen with server restarts and such.

The gap for Application_NO is 10,410,345 for a table with 2,320 rows

Transaction_Payment_NO jumped an astonishing 1,712,149,313 for a table with 685 records.

Any ideas on what could be causing such large and seemingly arbitrary jumps?

Identity value jumps on multiple tables

Improve this question
1
  • 1
    Are you sure these are identity columns and not from a sequence? A sequence is much easier to accidentally alter or use up a whole bunch of values with a single stored procedure call. What kind of testing was happening during the 2-3 hour gap from 9:15 to 12:16? That seems suspicious to me... as Josh suggested below, perhaps some parts of the tests are doing a lot of data operations and then rolling back (which would leave those gaps).
    – Aaron Bertrand
    Aug 19, 2020 at 15:48

1 Answer 1

Reset to default
5

Some possible culprits:

  1. the testing process creates many rows in a transaction, and then rolls back the transaction

This seems like the most likely reason, as you mentioned you're currently doing testing. Perhaps there are some automated tests being run that make changes to those tables, verify the results, and then roll back the changes.

Identity values are not reused after a transaction has been rolled back, which is what causes you to see large gaps in this scenario.

  1. Someone used the RESEED command

You can manually change the current "next identity" value with this command:

DBCC CHECKIDENT ('dbo.Transaction_Payment_NO', RESEED, 1712149313);

This one seems less likely, because you or one of the admins would have to go out of their way to do it. Similarly...

  1. Someone inserted those values manually

You can insert whatever you like into an IDENTITY column by first running this statement:

SET IDENTITY_INSERT dbo.Transaction_Payment_NO ON;
  1. Failovers and restarts

You mentioned this in the question, but just for completeness - SQL Server caches identity values in order to improve performance. But those pre-allocated identity values can be lost if the service restarts or an AG failover occurs. This leads to more predictable gaps though (10,000 on modern versions of SQL Server).

Improve this answer
1
  • As an extension to possibility 3: manually inserting values will effectively reseed the identity (like possibility 2) even if done in a transaction that is then rolled back, though only if the manually added values are ahead of the current seed. For example see sqlfiddle.com/#!18/9eecb/132722
    – David Spillett
    Aug 16, 2021 at 14:13

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

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