Sql Server 2012 Database identity increment suddenly started jumping to 100s

In one of my tables "Fee" in column "ReceiptNo" in Sql Server 2012 Database identity increment suddenly started jumping to 100s instead of 1 depending on the following two things.

1.  if it is 1205446 it is jumps to 1206306, if it is 1206321, it jumps to 1207306 and if it is 1207314, it jumps to 1208306. What I want to make you note is that the last three digits remain constant i.e 306 whenever the jumping occurs as shown in the following picture.

2. this problem occurs when I restart my computer
Nilesh SawantAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Vikas GargAssociate Principal EngineerCommented:

Please check the Identity seed and Identity increment property of the table.

The you can check the current status of identity by


Open in new window

This will give you current status for the identity

You can reseed the value using this query

DBCC CHECKIDENT (yourtable, reseed, yournewvalue)

Open in new window

Nilesh SawantAuthor Commented:
But issue not resolved. Please read question carefully,

Thanks for comment.
HuaMin ChenProblem resolverCommented:
I think there can be some other process try to do insert to the table, that leads to increment of the identity column, but finally the insert action does fail.
10 Tips to Protect Your Business from Ransomware

Did you know that ransomware is the most widespread, destructive malware in the world today? It accounts for 39% of all security breaches, with ransomware gangsters projected to make $11.5B in profits from online extortion by 2019.

David ToddSenior Database AdministratorCommented:

Did the server shut SQL down cleanly? I had this happen after some power outages on my desktop where SQL didn't shutdown cleanly. I now have a ups. But after the jump counter increments by 1 again.

Vitor MontalvãoMSSQL Senior EngineerCommented:
This is a known bug (even Microsoft doesn't admit it) and it's explained in Kale Delaney's article.
You have 2 ways to solve this:
1 - Add the -t272 trace flag to the SQL Server start parameter as explained in the above article
2 - Replace the IDENTITY solution for a SEQUENCE one
Scott PletcherSenior DBACommented:
It's not really a bug.  That's a deliberate approach.  Keep in mind that MS never guarantees or even implies that no values will be skipped.

For efficiency, SQL generates an in-memory pool of identity values whenever one is needed, rather than generating every value individually.  The pool size is 1,000, I think (from SQL 2005 on).  If SQL shuts down, the pool is lost and SQL generates a new pool from the last recorded value.

If gaps in the number are an issue for you, you need to use a different mechanism to assign values.

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.