Primary Key Jump

[vsmash]

G'day folks,

My sql server 2012 database is plodding along happily with its custom online store.
Yesterday it's primary key incremental column jumped from 60681 to 61678
then resumed incrementing by 1. It is now up to 61686 and chugging along again.

No apparent harm done but very strange behaviour. I'm looking into the logs but
I don't believe anyone has had at it. Anyone heard of this before?

[|-|x]

Yeah, I had a similar thing happen in one web app where I was using the same name for an SQL parameter in a couple different queries. It seems that somehow a value from another query was inadvertantly maintained in that variable holder in the MySQL Session and was used as the ID of a subsequent insert query. (hopefully that makes some kind of sense how I described it)

The upshot of it was that it overrode the auto increment, and everything went on fine until the autoincrement caught up and tried to reuse that key value. This caused a duplicate key error and the insert failed. I had to manually adjust the data to get the app back on track.

Whether this will be a problem for you, I suggest depends entirely on how your autoincrement fields are implemented if they will 'skip' existing values on their own.

[vsmash]

Thanks for your comments Mr Hearth. My autoincrement is in the database (is Identity) and the table in question is orders and never likely to increment by more than 100 per week. This jumped almost 1000 from one order to the next in an hour or so.

I can also add that this app was cruising along for the last 3 years on sql server 2008. A week before the occurrence, I had migrated from iis7 sql2008 to new machine iis7.5 sql2012. This migration appeared successful and the table in question had incremented by 96 on the new system before the hiccup. The old system had been switched off to insure there was definitely no code speaking to the wrong box.

Having said that I appreciate the comments and have a new prong in my investigation.

[vsmash]

It happened again on Monday.
Jumped from 61787 to 62786 between 9:07am and 2:47pm
Customer with order number 62786 reports that the site had been down for some time.

[|-|x]

Is there a chance that there could be any server maintenance or database/table optimisation, backup or occurring at these times which may have some affect on the indexes or hold an extended table lock?

I would recommend checking all server logs for the specified timeframe to see if there is anything that may indicate a cause.

I would also ask the customer to report immediately next time the site is down as catching a live event may yield clues to the root cause as well.

It is still possible that this could be a coding error such as a loop running too long or forgetting to release a lock or transaction, or unintended SQL session carryover.