Identity Column jumps massively from 168712 to 9999993

Question

Identity Column jumps massively from 168712 to 9999993

Perumal, Gowthami 5

I have table with identity column id. There was massive jump from id 168712 to 9999993. It has happened twice in different servers. Its not identity cache/transaction rollback/manual insert/ reseed. Why its happening. Please guide me on this.

Viorel 126.9K Reputation points

2025-10-17T16:58:41.7966667+00:00

What jump would be acceptable?

Answer recommended by moderator

1 additional answer

Your answer

Viorel 126.9K Reputation points

2025-10-17T16:58:41.7966667+00:00

What jump would be acceptable?

Answer 1

Hi @Perumal, Gowthami,

Given SQL Server 2019 Enterprise and SIMPLE recovery, this behavior is by design.

The only realistic cause of a jump from 168,712 to 9,999,993 is a failed or rolled-back large insert attempt from the application (bulk insert, batch insert, retry logic, background job, or parallel load). IDENTITY values are allocated up front and are never rolled back, even when the insert fails.

This can happen without any manual intervention and without data actually being inserted.

What it is not.

Recovery model
Corruption
Identity cache alone
SQL Server bug

One final note is that,

IDENTITY guarantees uniqueness, not continuity. Large gaps are expected under bulk/batch workloads.

If the number must be sequential, use a SEQUENCE with controlled allocation or a separate business key.

Thanks,
Akhil.

Answer 2

Erland Sommarskog 133.9K MVP Volunteer Moderator

Why would you care? I mean, if you cared, why did you pick a column property that means "I don't care"?

But apart from that, it is a little unexpected in so far that it does not seem to be due to the identity cache, since the cache is 1000 for int and 10000 for bigint.

But it could be due to DBCC CHECKIDENT or inserts with SET IDENITITY_INSERT ON. You seem to rule out these, but it is not clear to me why you can be such sure.

But may be most likely reason is a failed multi-row insert. If you attempt to insert 10 million rows, and this fails for some reason, you may consume 10 million IDENTITY values.

Akhil Gajavelly 1,820 Reputation points Microsoft External Staff Moderator

2025-10-23T11:20:29.5766667+00:00

Hi @Perumal, Gowthami ,

Just checking in, could you please confirm whether there were any bulk insert operations or ETL jobs that attempted large multi-row inserts around the time this jump occurred? Even if they failed, SQL Server would still reserve identity values for those attempted rows.

Also, when @Viorel asked “What jump would be acceptable?” could you share more on that? It might help others understand whether this is mainly a data gap concern or if it’s affecting downstream logic/reports.

Once we know that, we can guide you better on whether you need to control the sequence or just monitor future jumps.

Thanks,
Akhil.
Perumal, Gowthami 5 Reputation points

2025-10-23T11:36:50.5866667+00:00

We are using the db daily and we dont manually insert data to table, its only through the application.
Erland Sommarskog 133.9K Reputation points MVP Volunteer Moderator

2025-10-23T20:55:10.3+00:00

Or so you think. Often when unexpected things happens in the database, it is indeed due to manual intervention of some sort, not necessarily intentional.
Akhil Gajavelly 1,820 Reputation points Microsoft External Staff Moderator

2025-10-27T03:06:04.7433333+00:00

Hi @Perumal, Gowthami,

Thanks for confirming that inserts happen only through the application.

Since the @Erland Sommarskog pointed out, unexpected identity jumps are often caused by manual or system-level operations, it might be worth checking if any maintenance jobs, replication syncs, or bulk imports ran around that time, sometimes these can trigger large identity value gaps even when inserts are app-driven.

If possible, you could also share what SQL Server version and recovery model the database is running on; that might help the community narrow it down further.

Thanks,
Akhil.
Perumal, Gowthami 5 Reputation points

2025-11-10T12:14:48.2966667+00:00

Hi @Akhil Gajavelly

Sql Server Version : Microsoft SQL Server 2019 (RTM-CU32-GDR) (KB5065222) - 15.0.4445.1 (X64) Aug 13 2025 11:07:18 Copyright (C) 2019 Microsoft Corporation Enterprise Edition (64-bit) on Windows Server 2019 Standard 10.0 <X64> (Build 17763: ) (Hypervisor)

Recovery Model : SIMPLE
Akhil Gajavelly 1,820 Reputation points Microsoft External Staff Moderator

2026-03-24T04:43:06.38+00:00

Hi @Perumal, Gowthami ,

Just a quick follow-up on this. As mentioned earlier, the IDENTITY jump you observed is expected behavior due to pre-allocation and possible failed/rolled-back inserts. This is by design and not an issue with SQL Server.

We’ll assume this resolved your query. If you still have any questions, feel free to reply.

Thanks,
Akhil.

Share via

Identity Column jumps massively from 168712 to 9999993

1 additional answer

Your answer