Wednesday, May 26, 2010

SQL Azure and Session Tracing ID

by Wayne Walter Berry via SQL Azure Team Blog on 5/26/2010 4:24:11 PM

If you have been paying close attention, you will have noted that SQL Server Management Studio 2008 R2 has added a new property for connections to SQL Azure -- the Session Tracing ID.

A session tracing identifier is a unique GUID that is generated for every connection to SQL Azure. On the server side, the SQL Azure team tracks and logs all connections by the Session Tracing Id and any errors that arise from that connection. In other words, if you know your session identifier and have an error, Azure Developer Support can look-up the error in an attempt to determine what caused it.

SQL Server Management Studio

In SQL Server Management Studio, you can get your session tracing identifier in the properties window for the connection.

Transact-SQL

You can also ask for your Session Tracing ID directly in Transact-SQL using this query:

SELECT CONVERT(NVARCHAR(36), CONTEXT_INFO())

C#

Alternatively, you can use this C# code:

using (SqlConnection conn = new SqlConnection(…))
{
    // Grab sessionId from new connection
    using (SqlCommand cmd = conn.CreateCommand())
    {
        conn.Open();
         cmd.CommandText = "SELECT CONVERT(NVARCHAR(36), CONTEXT_INFO())";
        sessionId = new Guid(cmd.ExecuteScalar().ToString());
    }
}

It is important to note that the Session Tracing ID is per connection to the server, and ADO.NET pools connections on the client side. Which means that some instances of SqlConnection will have same Session Tracing Id, since the connection they represent is recycled from the connection pool.

Summary

If you have the Session Tracing ID, along with the server name and the approximate time when calling Azure Developer Support you can expedite the debugging process and save yourself valuable time. Do you have questions, concerns, comments? Post them below and we will try to address them.