1

In the middle of basic coding and testing we saw a huge non-patterned jump in Identity values for multiple tables but not all. We are unaware of any server blips or attempted bulk operations, but DBAs are looking into the logs. The gaps are not the typical 1,000 or 10,000 seen with server restarts and other IDENTITY-CACHE issues. 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? I initially asked on the DBA SE but realized this might be something on the dev side.

Identity value jumps

BikeMrown
  • 1,140
  • 2
  • 10
  • 17
  • 1) Have you read https://stackoverflow.com/questions/17587094/identity-column-value-suddenly-jumps-to-1001-in-sql-server and 2) Are you particularly concerned? – Caius Jard Aug 18 '20 at 16:04
  • OP is seeing enormous jumps, not the 1000 or 10,000 point jumps, as in the linked question – Alex Aug 18 '20 at 16:08
  • Not that the value *does* matter, but this suggests you have a very quick run of multiple improper stopping of the server (unlikely), you have a very large cache for your `IDENTITY` values (possible, though odd to be so large) or someone changed the seed(s) (more likely). The latter is more likely due to error and running code they (the user) don't understand and shouldn't be running. – Thom A Aug 18 '20 at 16:08
  • 1
    Yes, I've read any and every post I can find on Identity jump, including that. Best guess is someone doing an Identity Insert On script but there are no actual insertions to suggest it (the records before and after the jumps are the same developer doing operations through the web app) and we don't have any instances of that in code or sproc. – BikeMrown Aug 18 '20 at 16:13
  • If someone is using `IDENTITY_INSERT` and thus altering the seed, that would suggest the last guess I made in the comment above, @BikeMrown . – Thom A Aug 18 '20 at 16:24
  • 1
    Assuming you're not risking an overflow (meaning your identity data types are bigint and not int [because with an int you're cutting it quite close to the max value of 2,147,483,647]) - I wouldn't worry about it too much. Interested, yes, but not worried. – Zohar Peled Aug 18 '20 at 18:06
  • @ZoharPeled thanks for that reminder, the identity is in fact just an int so that was a great catch for us. – BikeMrown Aug 19 '20 at 17:04

1 Answers1

2

This looks like a Transaction Problem, you can see that 2178 is linked to 171215063. Meaning something did all those inserts but deleted them. Check the Transaction log.

https://www.sqlshack.com/how-to-read-a-sql-server-transaction-log/

juanvan
  • 671
  • 7
  • 19
  • 1
    This ended up being spot on. Turns out someone working on a different project from our but with access to our schema did a metric ton of scripting and deleting without telling anyone. Yay. File yet another one under "Organizational Ineptitude" – BikeMrown Aug 19 '20 at 17:05