Identity Column jumps massively from 168712 to 9999993

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.

We are using the db daily and we dont manually insert data to table, its only through the application.

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.

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.