Log SPID of a SqlConnection?

Question

_{Wednesday, January 9, 2013 5:49 PM}

Is there a good way to log the SQL Server SPID of a given ADO.NET SqlConnection?  Would be useful to have when SQL exeptions are thrown in ADO.NET and need to be tracked down (blocking/locking/timeouts/etc).

(.NET 4.5)

All replies (4)

_{Wednesday, January 9, 2013 6:59 PM ✅Answered | 1 vote}

You could SELECT `` ``@@SPID in the query and store the value of the ExecuteScalar call. Although, Im not sure if your SPID stays with your SqlConnection or changes each time you execute a new command, so you'll have to experiment.

Lot of info here: http://blog.sqlauthority.com/2009/07/19/sql-server-get-last-running-query-based-on-spid/

_{Wednesday, January 9, 2013 9:17 PM | 1 vote}

A SqlConnection object is a wrapper around the raw connection (where the SPID would be).  For the life of the SqlConnection it'll use the same raw connection (once it gets assigned) but when the SqlConnection is closed the raw connection gets added back to the pool.  The connection pool can have any # of raw connections (each with their own SPID).  Thus during the life of an application a single SPID can be associated with multiple SqlConnection instances (although never more than one at a time).

I don't know how useful the SPID would be for debugging though.  Please refer to this post (http://social.msdn.microsoft.com/Forums/en-US/sqlnetfx/thread/90ecf4bb-f101-4063-8846-e98610c71e56/) that discusses how to diagnose some of the issues you mentioned from within SQL itself rather than trying to track it from the .NET side.  If you enable profiling in SQL then you can capture the queries that are causing deadlocks and whatnot without ever having to touch the client side.

As for your base question there is no supported way of getting the underlying raw connection such that you could get the unique information you want given a SqlConnection. You might be able to come up with something if you were to reflect on the private members but I wouldn't recommend that approach.

Michael Taylor - 1/9/2013
http://msmvps.com/blogs/p3net
 

_{Wednesday, January 9, 2013 9:22 PM}

A SqlConnection object is a wrapper around the raw connection (where the SPID would be).  For the life of the SqlConnection it'll use the same raw connection (once it gets assigned) but when the SqlConnection is closed the raw connection gets added back to the pool.  The connection pool can have any # of raw connections (each with their own SPID).  Thus during the life of an application a single SPID can be associated with multiple SqlConnection instances (although never more than one at a time).

I don't know how useful the SPID would be for debugging though.  Please refer to this post (http://social.msdn.microsoft.com/Forums/en-US/sqlnetfx/thread/90ecf4bb-f101-4063-8846-e98610c71e56/) that discusses how to diagnose some of the issues you mentioned from within SQL itself rather than trying to track it from the .NET side.  If you enable profiling in SQL then you can capture the queries that are causing deadlocks and whatnot without ever having to touch the client side.

As for your base question there is no supported way of getting the underlying raw connection such that you could get the unique information you want given a SqlConnection. You might be able to come up with something if you were to reflect on the private members but I wouldn't recommend that approach.

Michael Taylor - 1/9/2013
http://msmvps.com/blogs/p3net
 

I figured it might be nice to have the SPID when logging errors.   Here is some code that appears to work.  It might be nice to implement this as an extension method to the SqlConnection object.

        private short? GetSPID(SqlCommand cmd)
        {
            short? retVal = null;

            if (cmd.Connection.State != ConnectionState.Open)
            {
                return retVal;
            }

            if (! this.SetSPID)
            {
                return retVal;  //if SetSPID is not enabled, bail out
            }

            using (SqlCommand cmd2  = new SqlCommand("SELECT @@SPID", cmd.Connection))
            {
                retVal = (short)cmd2.ExecuteScalar();
            }

            return retVal;
        }

Good info. Thanks

_{Friday, July 3, 2015 2:12 AM}

Looks like SPID may be different per SqlCommand invocation so don't think my previous idea is no good.  

.NET SqlConnection has a client connection ID that looked promising for sys.dm_exec_connections but it never resolves to any row in that view.  Not sure what benefit SqlConnection.ClientConnectionId provides.

I think the best you can hope to do is get the process ID and lookup into sys.dm_exec_session via procID.