Starting SQL Trace using C#

Everybody uses SQL Profiler tool to capture useful information against SQL Server. But is it possible to capture SQL Traces programmatically? Well, by and large there are two possible ways that I can think of. Firstly we can call TSQL stored procedues such as sp_trace_create in the program. Secondly, we can utilize Microsoft.SqlServer.Management.Trace classes (aka Trace Management Object (TMO)).

Microsoft.SqlServer.Management.Trace is implemented in Microsoft.SqlServer.ConnectionInfoExtended.dll which is in the GAC. Typically you will need Microsoft.SqlServer.ConnectionInfo.dll in order to establish SQL connection. So add references to two DLLs in your C# project. Now, here is a small sample that starts SQL Trace and saves the result to a trace file.

using System;
using System.IO;
using System.Reflection;
using Microsoft.SqlServer.Management.Common;
using Microsoft.SqlServer.Management.Trace;

namespace StartTrace
{
 class Program
 {
  static void Main(string[] args)
  {
    if (args.Length != 2)
    {
      string asmName = Assembly.GetExecutingAssembly().GetName().Name;
      Console.WriteLine("Usage: {0}.exe <serverName> <traceFile>", asmName);
      Console.WriteLine(@"   ex) {0}.exe TestServer test.trc", asmName);
      return;
    }
    string serverName = args[0];
    string traceFile = args[1];
    (new Program()).StartTrace(serverName, traceFile);
 }

 void StartTrace(string serverName, string traceFile)
 {
  // Sanity check
  if (string.IsNullOrEmpty(serverName))
   throw new ArgumentException("serverName is null or empty");
  if (string.IsNullOrEmpty(traceFile))
   throw new ArgumentException("traceFile is null or empty");
  if (File.Exists(traceFile))
  {
    Console.WriteLine("### WARNING ### '{0}' already exists. Press Q if you don't want to overwrite.", traceFile);
  }

  // Start TraceServer   : NOTE: change TDF path below if needed
  TraceServer trcSvr = new TraceServer();
  SqlConnectionInfo ci = new SqlConnectionInfo(serverName);
  string tdf = @"%ProgramFiles%\Microsoft SQL Server\100\Tools\Profiler\Templates\Microsoft SQL Server\100\Standard.tdf";
  tdf = Environment.ExpandEnvironmentVariables(tdf);
  trcSvr.InitializeAsReader(ci, tdf);

  // Press S to stop tracing, Q for quit.
  Console.WriteLine();
  Console.WriteLine("TRACE started at {0} : Server={1}, TraceFile={2}", DateTime.Now, serverName, traceFile);
  Console.WriteLine("Press 'S' to stop trace... Press 'Q' to quit...");
  while (true)
  {
   ConsoleKeyInfo key = Console.ReadKey();
   if (key.Key == ConsoleKey.Q)
   {
    trcSvr.Stop();
    return;
   }
   if (key.Key == ConsoleKey.S)  // Stop trace
   {
     trcSvr.Stop();
     break;
   }
  }
  Console.WriteLine();

  // Write trace to .trc file
  TraceFile writer = new TraceFile();
  writer.InitializeAsWriter(trcSvr, traceFile);
  writer.WriteNotify += new WriteNotifyEventHandler(writer_WriteNotify);
  while (writer.Write()) ;
  writer.Close();
 }

  // Optional Notify handler
  void writer_WriteNotify(object sender, TraceEventArgs args)
  {
  Console.WriteLine("Writing: {0}" , args.CurrentRecord[0]);
  args.SkipRecord = false;
  }
 }
}

In order to start SQL Trace, I created TraceServer object and called InitializeAsReader() method with SQL connection info and profiler template file (in this case, I used stanard.tdf. If you want to have replayable trace file, you have to choose tsql_replay.tdf template). Once InitializeAsReader is successful, it automatically starts SQL trace again the SQL Server. So now you can send some TSQL statements to the SQL server. When everything is done and you want to stop trace, you press S as source code tells you. Then, next it will write SQL trace data to the output .trc file. I on purpose added WriteNotify event handler just to show you that you can check each trace. If you want to omit unncessary trace data during trace write, this is the place you can control by setting SkipRecord = true.

You can also save the trace results to SQL Table by using TraceTable class and also can replay trace by using TraceReplay class.

NOTE: Sample tool using TMO can be downloaded from here Profiler Console