How to read a SQL Server trace file using .Net code, TraceFile and TraceServer.
When you wish to trace and see what is going on in SQL Server then the SQL Profiler tool is really useful.
But what if you wish to read it from, for example, a C# console application? With or without having SQL Profiler installed.
How do you do this? For this post we’ll assume that there is no SQL Profiler installed.
We’ll start with creating a trace from SQL Server Management Studio. Execute the following:
declare @TraceId int
-- create the trace
exec sp_trace_create @TraceId output, 0, N'C:\Temp\OurDemoTrace'
-- set event and columns to trace
exec sp_trace_setevent @TraceId, 12, 1, 1
exec sp_trace_setevent @TraceId, 12, 6, 1
exec sp_trace_setevent @TraceId, 13, 1, 1
exec sp_trace_setevent @TraceId, 13, 6, 1
-- start the trace
exec sp_trace_setstatus @TraceId, 1
-- check status for our trace
select value from fn_trace_getinfo(@TraceId) where property in (2, 5)
go
A short explanation on the above SQL:
First we create the trace (this doesn’t start it) this will give us the server generated traceid, we run it with no options and the file created as OurDemoTrace in the C:\Temp directory,
the .trc extension will be added automatically. Note that if there is already a file on disk with the same name, you will get an error:
Msg 19067, Level 16, State 1, Procedure sp_trace_create, Line 1
Cannot create a new trace because the trace file path is found in the existing traces.
Secondly we specify what events and columns we want to be ON for this trace. In this case I have chosen the SQL:BatchCompleted (12) and SQL:BatchStarting (13) events.
I have also chosen to include the TextData column (1) and the NTUserName column (6). The 1 means ON.
Thirdly we start the trace by setting the status to ON (1) and finally we just check the status of this trace, property 2 is the filename and property 5 is the current trace status where 1 = running.
So, now we should have a trace up and running and we would like to read the information in this trace.
There could be two scenarios on how we want to do this.
.1 We want to just read what we have in the trace file at this stage (or read an old non active trace).
.2 We want to listen in on the trace and get hold of new events that enters the trace. I.e. much like SQL Profiler.
I will show how to do both.
First, create a new C# console application. Then what you need to do (for both scenarios) is to add a reference to the following assemblies:
Microsoft.SqlServer.ConnectionInfo
Microsoft.SqlServer.ConnectionInfoExtended
These contain the necessary classes for doing this.
NOTE: The classes uses are 32 bit, so if you are running on a 64 bit machine, you need to build the application for x86.
So, the first scenario is where we want to read through a known trace file as it is, in this case we do not care about events that may occur later.
static void Main(string[] args)
{
TraceFile tf = new TraceFile();
string trcFile = @"C:\Temp\OurDemoTrace.trc";
try
{
tf.InitializeAsReader(trcFile);
StringBuilder sb;
while (tf.Read())
{ sb = new StringBuilder();
sb.AppendLine("\nEventClass: " + tf.GetString(tf.GetOrdinal("EventClass")));
sb.AppendLine("NTUserName: " + tf.GetString(tf.GetOrdinal("NTUserName")));
sb.AppendLine(tf.GetString(tf.GetOrdinal("TextData")));
Console.WriteLine(sb.ToString());
}
catch (Exception ex)
{
Console.WriteLine(ex);
}
finally
{
tf.Close();
}
}
This code should be self-explanatory, but in short, we use the TraceFile class and initialize it as a reader providing the .trc file that we are interested in.
We then call Read() until we reach the end of the file selecting the EventClass, TextData and NTUserName columns.
So, what happens when a new event occurs in the trace file, well, you have to reread the trace file from the start, going through all the data again.
This may be fine, but if you wish to listen in on the trace in a ‘live’ fashion, then enter the second scenario.
So, the second scenario is where we want to listen into trace and we want the application to automatically update when there is a new even happening.
Simply replace the code above with:
static void Main(string[] args)
{
TraceServer ts = new TraceServer();
string trcFile = @"C:\Temp\OurDemoTrace.trc";
try
{
SqlConnectionInfo sci = new SqlConnectionInfo();
sci.ServerName = @"<your server>\<your instance>";
sci.DatabaseName = @"<your database>";
// Use integrated security:
sci.UseIntegratedSecurity = true;
// Or SQL Authentication:
//sci.UserName = "user";
//sci.Password = "password";
ts.InitializeAsReader(sci, trcFile);
StringBuilder sb;
while (ts.Read())
{
sb = new StringBuilder();
sb.AppendLine("\nEventClass: " + ts.GetString(ts.GetOrdinal("EventClass")));
sb.AppendLine("NTUserName: " + ts.GetString(ts.GetOrdinal("NTUserName")));
sb.AppendLine(ts.GetString(ts.GetOrdinal("TextData")));
Console.WriteLine(sb.ToString());
}
}
catch (Exception ex)
{
Console.WriteLine(ex);
}
finally
{
ts.Stop();
ts.Close();
}
}
Again, this should be self-explanatory, but here we use a TraceServer instance instead. This will be open and listen for incoming events rather than reading
through the trace file from start to end. So run the application and do, for example, as SELECT on a table in your database and it should show.
Since the first scenario reads from start to end and don’t listen to new events and the second scenario don’t read from start to end but listens on new events
perhaps a combination of the two may be best for you.
Oh, you may want to stop the trace on the server when you are done J
Simply run this in SQL Server Management Studio:
declare @TraceToStop int
select @TraceToStop = TraceId from fn_trace_getinfo(default) where value = N'C:\Temp\OurDemoTrace.trc'
exec sp_trace_setstatus @TraceToStop, 0
exec sp_trace_setstatus @TraceToStop, 2
TraceFile Class
https://msdn.microsoft.com/en-us/library/microsoft.sqlserver.management.trace.tracefile.aspx
SqlConnectionInfo Class
TraceServer Class
https://msdn.microsoft.com/en-us/library/microsoft.sqlserver.management.trace.traceserver.aspx
sp_trace_create (Transact-SQL)
https://msdn.microsoft.com/en-us/library/ms190362.aspx
sp_trace_setevent (Transact-SQL)
https://msdn.microsoft.com/en-us/library/ms186265.aspx
sp_trace_setstatus (Transact-SQL)
https://msdn.microsoft.com/en-us/library/ms176034.aspx
fn_trace_getinfo (Transact-SQL)
https://msdn.microsoft.com/en-us/library/ms173875.aspx
Using SQL Server Profiler
https://msdn.microsoft.com/en-us/library/ms187929.aspx
Comments
Anonymous
July 16, 2010
I had to look up a couple of namespaces and figure out how to add the assemblies to vs express, but otherwise, very cool way to monitor client queries in a test environment. Thanks!!Anonymous
July 16, 2010
The comment has been removed