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.
