12.4. Tracing

The SMO trace and replay classes provide an interface with which to trace and record events, manipulate and analyze trace data, and replay recorded trace events. SQL Profiler and the SQL Trace system stored procedures can also perform these tasks.

The SMO classes used to manage trace and replay operations are described in Table 12-5. These classes are located in the Microsoft.SqlServer.Management.Trace namespace.

Table 12-5. SMO classes for managing trace and replay operations

Class	Description
ReplayEventArgs	Represents arguments used to report events that occur during replay operations.
traceEventArgs	Represents arguments used to report events that occur during trace operations.
TRaceFile	Represents a trace logfile.
traceReplay	Represents a replay operation for trace logfiles and tables.
TRaceReplayOptions	Represents configuration settings for replaying a trace. The Options property of the traceReplay class returns a traceReplayOptions object for the replay operation.
TRaceServer	Represents a new trace on a SQL Server instance.
traceTable	Represents a table of trace information. The OutputTable property of the traceReplay class returns a traceTable object for the replay operation.

The examples in this section show how to programmatically use the SMO trace classes to capture and replay trace events.

The first example logs the name of the first 20 trace log events to the console using the standard trace definition file Standard.tdf, installed by default in the C:\Program Files\Microsoft SQL Server\90\Tools\Profiler\Templates\Microsoft SQL Server\90 directory:

    using System;

    using Microsoft.SqlServer.Management.Common;
    using Microsoft.SqlServer.Management.Smo;
    using Microsoft.SqlServer.Management.Trace;

    class Program
    {
        static void Main(string[] args)
        {
            TraceServer ts = new TraceServer(  );

            ConnectionInfoBase ci = new SqlConnectionInfo("localhost");
            ((SqlConnectionInfo)ci).UseIntegratedSecurity = true;
            ts.InitializeAsReader(ci,
              @"C:\Program Files\Microsoft SQL Server\90\Tools\Profiler\" +
              @"Templates\Microsoft SQL Server\90\Standard.tdf");

            int eventNumber = 0;
            while (ts.Read(  ))
            {
                Console.Write(ts.GetValue(0) + Environment.NewLine);
                eventNumber++;
                if (eventNumber == 20)
                    break;
            }
            ts.Close(  );

            Console.WriteLine(Environment.NewLine + "Press any key to continue.");
            Console.ReadKey(  );
        }
    }

Results are shown in Figure 12-9.

Figure 12-9. Results for tracing example

The trace definition file determines the information contained in the tracewhich events and what columns of trace data are captured for each event. This example displays only the first columnthe name of the trace event. You can get a complete list of columns by viewing the standard definition file using SQL Server Profiler.

The next example replays an existing trace logfile. Use an existing trace file or create a trace file to use with SQL Server Profiler by following these steps:

The source code for the example follows:

    using System;
    using System.Data;
    using System.Collections;

    using Microsoft.SqlServer.Management.Common;
    using Microsoft.SqlServer.Management.Smo;
    using Microsoft.SqlServer.Management.Trace;

    class Program
    {
        static void Main(string[] args)
        {
            TraceReplay tr = new TraceReplay(  );
            TraceFile tf = new TraceFile(  );
            tf.InitializeAsReader(@"C:\PSS2005\Trace\TestTrace.trc");
            tr.Source = tf;
            tr.Connection = new SqlConnectionInfo("localhost");
            tr.ReplayEvent += new ReplayEventHandler(tr_ReplayEvent);
            tr.Start(  );

            Console.WriteLine(Environment.NewLine + "Press any key to continue.");
            Console.ReadKey(  );
        }

        static void tr_ReplayEvent(object sender, ReplayEventArgs args)
        {
            Console.WriteLine("--- Record number: " + args.RecordNumber + " ---");
            for (int i = 0; i < args.CurrentRecord.FieldCount; i++)
                Console.WriteLine(args.CurrentRecord[i].ToString(  ));

            Console.WriteLine(  );
        }
    }

Partial results are shown in Figure 12-10. Of course, your results will be slightly different.

The example displays all of the data in the trace file. In this example, there are 17 records, the last 2 of which are shown.