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 (3)

  1. LarryChesnut says:

    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. 

  2. rogerlin says:

    Hi Bob,

    In Profiler trace we only have Audit Logout and Audit Login events seen genrated by the sp_reset_connection calls and these events don’t have Eventsubclass. I can’t find the disconnect and connect event in the profiler trace, which group they should be under? My concern is in the trace these Logout events will have large number of Reads and Writes amounts and they look like are accumulative for the same SPID. However, the cpu amount is not accumulative, why is it not consistent? Looking at these Logout and Login event, there is no EventSubClass, how do we know or filter them out as they are only a logical event?

    This is quite important behavior change in the profiler trace and can really cause concern for the huge number of logout and login events now been gererated by the sp_reset_connections. Do we have a KB article or a white pager for that? I had hard time to figure out what’s going on until I found your blog. We would really appreciate if SQL Server group or PSS can provide more detailed information on this issue in an official way.



  3. rogerlin says:

    Correct one thing here that by default the EventSubClass column for the Audit Logout and Login was not checked. Had to check show all columns before can select that column.



Skip to main content