How to monitor activity on a single table using SQL Server profiler

Solution 1:

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.

Solution 2:

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...