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 GargBusiness Intelligence DeveloperCommented:
Hi,

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

The you can check the current status of identity by

DBCC CHECKIDENT (YourTableName, NORESEED)

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

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

Thanks for comment.
0
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.
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

David ToddSenior DBACommented:
Hi,

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.

Regards
  David
0
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
0
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.
0

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.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.