5

I have a table with an int identity as the PK. Something is causing it to increment, and I'd like to know what that is.

To give a quick example, yesterday I inserted a record and got ID 41,773. Today I inserted a new record and I got ID 44,898. There are no records with IDs between these.

There are lots of apps with access to the database, so I'm in the process of checking their logs for errors; however, I'm wondering if there is a way I can find out what queries are causing the IDENTITY increment without a record being inserted.

There are no explicit deletes on this table either, so I know its not an insert + delete combo, causing this, it has to be something failing.

Improve this question
5
  • My idea being that if I can find out the query causing the identity to increment, I can identify the application based on the nature of the query.
    – Nate
    Commented Feb 28, 2012 at 19:11
  • 1
    Interesting prob. How about transactions that ultimately rollback? Just a thought since I'm not sure that an uncommitted tran would bump the seed. I'd profile it :)
    – Eric Higgins
    Commented Feb 28, 2012 at 19:25
  • Any suggestions on how to go about doing that? Is there a report or logging I can turn on?
    – Nate
    Commented Feb 28, 2012 at 19:34
  • 2
    It definitely does increment even on a rollback. Just tested in SS2008r2
    – JNK
    Commented Feb 28, 2012 at 19:38
  • 1
    Thanks JNK, that confirms it then. I'll make an answer with some profiler info.
    – Eric Higgins
    Commented Feb 28, 2012 at 19:41

2 Answers 2

Reset to default
7

The most likely scenario based on the test JNK did, is transactions that are rolling back, but still causing the seed to increment.

The best route is to trace (profile) the activity so you can catch the offending transaction(s). Here's a starting point for using Profiler:

http://msdn.microsoft.com/en-us/library/ms181091.aspx

When you're starting to learn tracing, err on the side of tracing less than you think you need. tracing is "invasive", and you can imperil a DB instance if you go after too much info.

In this case you're looking for transaction based info. Feel free to post back if you have specific questions while setting up your trace. Note that you'll probably want to log to files unless you can leave the GUI up overnight. Logging to files is preferred overall unless you're trying to catch something in real time.

Improve this answer
2

The identity value will be increased if you begin a transaction, insert a row, then rollback the transaction.

I originally thought that Access databases could be a culprit in this, thinking that its column default retrieval function could be causing this behavior (if you started editing a new row but abandoned it without saving). However, I did some testing and in both ADPs and regular linked tables in MDBs/ACCDBs it does NOT cause artificial increasing of the identity value.

Improve this answer
2
  • Would you rephrase or explain your last statement: Access will round trip to the database to retrieve the value in order to allow access to it. Thanks.
    – nuux
    Commented Mar 1, 2012 at 15:42
  • Please see update.
    – ErikE
    Commented Mar 1, 2012 at 22:00

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.