How can I track calling code in a SQL Server trace?

Question

I'd like to use SQL Server traces to track more context about what code is using the database. I was going to use the "Application Name" property on the connection string. That looks something like the following:

object CallingObject; //set elsewhere
SqlConnectionStringBuilder connectionString = GetConnectionString();
connectionString.ApplicationName = CallingObject.GetType().ToString();
using (SqlConnection connection = new SqlConnection(connectionString.ToString()))
{
    // do your thing
}

Except that the large variety of resulting connection strings would mean that .net's Connection Pooling is no longer effective.

How can I track calling code in a SQL Trace without losing the benefit of connection pooling?

Answer 1

As long as you're using SQL Server, and as long as you have (or can have) centralized connection-creation logic, you can use the CONTEXT_INFO feature: http://msdn.microsoft.com/en-us/library/ms187768.aspx

We do something like this to track DB connections in a multi-user server-side application. Every time a new DB connection is created (reused from connection pooling, really, but "created" in ADODB/ADO.Net code):

DECLARE @ContextInfoBinary binary(128); 
SET @ContextInfoBinary = Convert(Binary(128), 'XX' + Replicate(Char(0), 36) + 'Up to 90 characters of connection info - username, page, action, etc') 
SET CONTEXT_INFO @ContextInfoBinary

Later, when you want to "track" the connections, you can convert the specified range of the context-info back to VarChar content:

SELECT RTRIM(hostname) AS hostname
    , hostprocess
    , RTRIM(program_name) AS program_name
    , CASE 
        WHEN SubString(Context_Info, 1, 2) = 0x5858
            THEN Replace(Convert(VarChar(90), SubString(Context_Info, 39, 90)), CHAR(0), '')
        ELSE 'UNKNOWN' 
        END AS ExtendedConnectionDescription
    --More stuff here, depending on what you're looking for
FROM master.sys.sysprocesses 

Some considerations:

• We moved to this concept specifically because of connection-pooling issues with the Application Name feature. It's important to us to be able to track blocking causes down to specific users and processes, at any given point in time.
• This adds a DB command to be executed for every connection created - this may or may not represent significant overhead, depending on your application design
• This sample uses VarChar (up to 90 characters); if you need to track NVarChar(extended character) data, your space drops to 45 characters
• In this example we're leaving 36 bytes that could be used for some other purpose, eg for preventing triggers from executing for some connections.
• You can output the value to a trace explicitly using sp_trace_generateevent, as outlined in this related question: How do you access the Context_Info() variable in SQL2005 Profiler?

---

UPDATE:

Only after re-reading your question did I realize you seem to be explicitly looking to add info to Traces only, and not so much for ad-hoc analysis of your current connections (which is more what I associated the use of "Application Name" with, sorry); the only useful approach I've seen specifically is the sp_trace_generateevent call. If you're going to do this, I'd advise you to also add the connection_info, as it won't cost you any more (you alsready have the db round-trip for sp_trace_generateevent) and will definitely help you with other types of analysis later on.