Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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.

share|improve this question
    
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 Feb 28 '12 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 Feb 28 '12 at 19:25
    
Any suggestions on how to go about doing that? Is there a report or logging I can turn on? –  Nate Feb 28 '12 at 19:34
2  
It definitely does increment even on a rollback. Just tested in SS2008r2 –  JNK Feb 28 '12 at 19:38
1  
Thanks JNK, that confirms it then. I'll make an answer with some profiler info. –  Eric Higgins Feb 28 '12 at 19:41

2 Answers 2

up vote 7 down vote accepted

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.

share|improve this answer

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.

share|improve this answer
    
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 Mar 1 '12 at 15:42
    
Please see update. –  ErikE Mar 1 '12 at 22:00

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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