Share via

MASSIVE Identity column value jump in SQL Server 2014

Mike Brown 1 Reputation point
2020-08-18T15:54:17.713+00:00

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 and 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.

18375-untitled.png

SQL Server
SQL Server
A family of Microsoft relational database management and analysis systems for e-commerce, line-of-business, and data warehousing solutions.
13,366 questions

5 answers

Sort by: Most helpful
Most helpful Newest Oldest
  1. Tom Phillips 17,721 Reputation points
    2020-08-18T18:23:21.74+00:00

    There are many reasons for skips in identity values. There were also bugs in SQL Server which caused large skips.

    The first thing to do is verify you are running the current version. Please see: https://support.microsoft.com/en-us/help/321185/how-to-determine-the-version-edition-and-update-level-of-sql-server-an

    1 person found this answer helpful.
    0 comments
  2. Tom Phillips 17,721 Reputation points
    2020-08-18T18:35:07.797+00:00

    It is also possible someone reseeded the value.

    1 person found this answer helpful.
    0 comments
  3. Sreekanth Madambath 86 Reputation points
    2020-08-18T15:56:51.973+00:00

    Probably any data load would be causing the case.

    0 comments
  4. Sreekanth Madambath 86 Reputation points
    2020-08-18T16:00:21.953+00:00
    0 comments
  5. Cris Zhan-MSFT 6,616 Reputation points
    2020-08-19T07:32:50.107+00:00

    Hi,
    I will vote the answer in this post:

    https://dba.stackexchange.com/questions/273935/massive-identity-column-value-jump-in-sql-server-2014

    If the response helped, do "Accept Answer" and upvote it.

    0 comments