Programmatically receiving profiler events (in real time) from SQL Server 2005

The SQL Server engine provides two basic types of tracing: profiler (or real time) tracing and file tracing. Actually, there are three more types of tracing: event notifications, WMI events and ETW trace, however they support only a subset of all available SQL trace events.

In this post I will show you how to leverage the new .NET classes provided in the Microsoft.SqlServer.Management.Trace namespace to start and stop profiler traces and receive events in real time from SQL Server 2005.

Managing of file traces programmatically has been possible since SQL Server 2000 through various stored procedures and functions (sp_trace_create, fn_trace_gettable, etc.). Profiler (or real time) events were also supported in SQL Server 2000 however they were only available through the Profiler tool (UI). The API used by the Profiler tool to communicate with SQL Server was never documented. The API is still not documented, however now we have .NET classes (built on top of this same API) providing similar functionality.

I will demonstrate a simple C# program which connects to SQL Server and shows the text of all starting batches in real time as the requests are coming to SQL Server.

First you need to connect to SQL Server. Here is how to connect to the local default instance of SQL Server:

  // Connect to the local default instance of SQL Server

  SqlConnectionInfo connectionInfo = new SqlConnectionInfo();

  connectionInfo.UseIntegratedSecurity = true;

Then you need to create a trace object and initialize it as reader by passing the connection info and a trace definition file. A trace definition file is a file generated by the SQL Profiler tool. It contains the list of events and columns you want to capture.

  // Create the trace object

  TraceServer trace = new TraceServer();

  // Start a trace using the trace definition file

  // (passed as first command line parameters)

  trace.InitializeAsReader(connectionInfo, args[0]);

Now, the only thing left is to read the trace events:

  // Reading events

  while (trace.Read())

  {

    Console.WriteLine("Event : " + trace["EventClass"]);

    Console.WriteLine("SPID : " + trace["SPID"]);

    Console.WriteLine("Login : " + trace["SessionLoginName"]);

    Console.WriteLine("Object: " + trace["ObjectName"]);

    Console.WriteLine("Text : " + trace["TextData"]);

    Console.WriteLine();

  }

You need to make sure that when you are done reading events you stop and close the profiler trace. Otherwise it will continue to stay on SQL Server. Eventually it will get cleaned up (10 minutes after SQL Server detects that there is no active reader):

  trace.Stop();

  trace.Close();

You can see all SQL Server traces by selecting from sys.traces. You can use sp_trace_setstatus to stop and close traces manually (if you need to).

Here is how to generate a trace definition file using SQL Server Profiler. First start SQL Server Profiler. Select File -> New Trace. Then connect to SQL Server. In the “General” tab,  for “Use the template” select “Blank”. Then click on the “Events Selection” tab and select “SQL:BatchStarting” (or whatever other events you want). Click “Run”. The trace will start. Now select File -> Save As -> Trace Template. Give it a name and save it. The trace template is now stored in your “Documents and Settings” folder. Now, instead of searching there you can simply export the template to a location you want by selecting File -> Templates -> Export Template.

With this post I’m attaching a working Visual Studio 2005 project showing the things I mentioned above. In the project I’m also showing how to capture Ctrl-C and Ctrl-Break console events and clean up any traces left after abnormal program termination.

example.zip

Comments

• Anonymous
  June 01, 2006
  Thanks for posting on this!
• Anonymous
  August 28, 2006
  Hi,
  
  This is great news, but the documentation for the trace namespace (http://msdn2.microsoft.com/en-us/library/ms212697.aspx) says that this method will not work against 64bit platforms (it says "Note The Trace and Replay objects are not supported on a 64-bit platform." without any further details)
  
  Are you able to comment on this?  
  
  Are we required to fallback to usage of sp_trace_create in these instances?  And if so, is there anyway to remotely determine the class of remote SQL Server 2005 (ie, 32 or 64 bit)?
  
  Thanks in advance
• Anonymous
  August 29, 2006
  Yes, this method only works for 32-bit applications. In order to do their job, the .NET classes need to load a native DLL provided by Profiler. Unfortunately, at the moment, Profiler is only available as 32-bit application, so 64-bit processes can not load any of it DLLs.
  
  The workaround for 64-bit platforms is to use WOW (run as 32-bit application). This is how Profiler works today.
  
  Thanks,
  -Ivan