Section 12.4. Tracing


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:

  1. Open SQL Server Profiler by selecting Start Microsoft SQL Server 2005 Performance Tools SQL Server Profiler.

  2. New Trace.

  3. Accept the defaults for the Trace Properties dialog box and click the Run button.

  4. Let the trace run for about 20 events and then stop it by selecting File Stop Trace.

  5. Save. Save the trace file as C:\PSS2005\Trace\TestTrace.trc and click the Save button.

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.



Programming SQL Server 2005
Programming SQL Server 2005
ISBN: 0596004796
EAN: 2147483647
Year: 2007
Pages: 147
Authors: Bill Hamilton

Similar book on Amazon

flylib.com © 2008-2017.
If you may any questions please contact us: flylib@qtcs.net