SQL Server 2005 SP2 – Trace Event Change – Connection Based Events

Starting with SQL Server 2005 SP2 the connection based events are also produced for sp_reset_connection activities, indicating the connection was reset. The pattern will look like the following.

RPC:Starting sp_reset_connection
Disconnect Subclass = 2
RPC:Completed sp_reset_connection
Connect Subclass = 2

The subclass = 2 indicates that the connection based events are not physical connection activities but the logical reset operations.

When attempting a reply you will first want to filter out the Connect and Disconnect events with subclass = 2.

- Bob Dorr

Comments

• Anonymous
  April 04, 2007
  Bob, your posting is very interesting, but after reviewing BOL and the Profiler tool I am not able to figure out exactly how we are supposed to filter out sp_reset_connection.  Did you mean to say the "Subclass" value could be found in the trace column EventSubclass?  If so, that column is not part of RPC:Starting or RPC:Completed. Could you show us an example of filtering this activity out? Here is my attempt: exec sp_trace_setfilter @TraceID, 21, 0, 1, 2 Thank you, Larry C. [RDORR] It is the EventSubClass for the Connect and Disconnect events and not the RPC event. 

• Anonymous
  January 31, 2008
  The comment has been removed

• Anonymous
  February 01, 2008
  The comment has been removed