identity increments by 10,000 when it was supposed to be 1

Question

Post reply

identity increments by 10,000 when it was supposed to be 1

stan

Ten Centuries

Points: 1394
More actions
June 16, 2026 at 10:46 am

#4866738
hi a peer of mine who ive never known to be wrong says a new table he created with pk identity(1,1) jumped by 10000 on one insert. has anyone ever seen this anomaly?
- This topic was modified 3 weeks, 3 days ago by stan.

Viewing 7 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply

Phil Parkin SSC Guru Points: 247225 More actions · Answer 1

Ask him for the code to reproduce the problem.

Also ask whether the server was restarted between inserts.

frederico_fonseca SSCoach Points: 16331 More actions · Answer 2

and its possible that someone did a insert with a high value and used identity insert - that would set the identity value to that of the insert

stan Ten Centuries Points: 1394 More actions · Answer 3

i ~~believe our infrastructure folks told him there was a server failure around the time of his inserts. i'll post back here once i get some details.~~

correction , no failures during the time he saw the problem occur.

This reply was modified 3 weeks, 3 days ago by stan.

ratbak Default port Points: 1435 More actions · Answer 4

Since the jump was 10000, the datatype is presumably bigint (int cache size is 1000).

stan Ten Centuries Points: 1394 More actions · Answer 5

yes, i believe he uses bigint as his pk's. do you think that has something to do with this?

ratbak Default port Points: 1435 More actions · Answer 6

yes, i believe he uses bigint as his pk's. do you think that has something to do with this?

No, that's just the behavior. An int column would have jumped by 1000.

Is there a process that is constantly failing or rolling back transactions?

No server failures. Was SQL Server service restarted? AG failover?

Unless the table has exhausted values -- and it's a bigint, so that's impossible unless server blips constantly (Nobody is storing 18 quintillion rows in SQL Server... or any RDBMS).

How many rows are inserted per day?

This reply was modified 3 weeks, 3 days ago by ratbak.