8

I have a table which uses and identity seed for its unique identity column. We have noticed some massive jumps in the identity value which we are not able to explain. The table has 20,000 odd rows, the maximin identity value is over 560,000,000 and the identity increment is at a value of 1!

I wanted to use SQL Server profiler to monitor activity on just that table so i could see what was causing the identity to jump in large incerments but when i came to look at the monitor i was instantly confused. I admit that i am in a rush and don't have time to really learn how this work (as much as i would like to) so could use a rough guide as to the steps you should take to monitoring activity on a table.

Any help would really be appreciated.

Improve this question

3 Answers 3

Reset to default
5

Start up SQL Profiler, create a new trace and connect to the SQL Server you want to monitor.

Caution, if this is very busy production server then you should not use SQL Profiler as it will slow the SQL Server down

I typically use the "standard" profile and then edit it.

Click on the "Events Selection" tab. I usually uncheck "Audit login", "Audit logout", "ExistingConnections" and "RPC Batch starting". This gives a nice clean trace output.

Make sure "TextData" is checked.

You can add a filter to the trace of "LIKE %%" to the TextData column, but this will only include SQL statements sent directly to the server. If there are stored procedures then you need to know which stored procedures touch your table and filter them.

If you ave cursor queries then you will get lots of "sp_fetch" stuff. You need to search for the DECLARE CURSOR statement with the same cursor ID.

Thinking about it, a bad cursor-for loop can quickly insert many thousands of "error" records and that may be a cause of these large increment jumps.

Improve this answer
1

The identity value will increment on every insert, even if the insert fails.

If you have a check or foreign key constraint, each failure will increment the identity. If you have a "value too large" then the insert will fail but the identity will increase.

Generally, this is not a problem. The purpose of the identity field is to provide a unique reference for that row and having it increment nicely with no missing numbers is a human "keep it neat" thing and not something the database will get upset about.

The more pressing issue is why your inserts are failing and you don't know about it...

Improve this answer
1
  • I'm well aware of the differet reasons for the identity seed increasing. What i want to understand using SQL Server Profiler is why and what i am asking for is guidance on how to set this up. What i find alarming about the jump is that they can be increments of over a million and i know that nothing in the system is trying to access this that many times - its only an employee table!
    – user22785
    Feb 10, 2010 at 15:13
0

SQL Server is resetting the identity column on the stop and start of the service. there's a connect item where someone thought it was a bug, but MS things this is a nice design feature. https://connect.microsoft.com/SQLServer/feedback/details/739013/alwayson-fail

There is a trace flag that turns off the behavior of reseeding the identity adding -T272 as a start up flag will help.

Improve this answer

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .