Saving Traces

Saving a trace involves one of these actions:

• Saving the captured event data to a specified place
• Saving the definition of the trace

Saving Event Data

The event data captured in a trace can be saved either to a file, a Microsoft® SQL Server™ table, or the Microsoft Windows NT® application log, or forwarded to another computer running SQL Server.

Note Only the extended stored procedures can be used to save event data to the Windows NT application log or to forward event data to another computer running SQL Server. Additionally, when SQL Server is running on Microsoft Windows® 95/98, the Windows NT application log cannot be specified as a destination for the trace queue data because Windows 95/98 does not have an event log.

Saving the captured event data to a file or SQL Server table is useful when you need to analyze or replay the captured data at a later time, such as for trend forecasting or troubleshooting and debugging application problems. For example:

• Using a trace file or trace table allows you to create a workload that is used as input for the Index Tuning Wizard.
• When trying to resolve a problem on your computer that requires analysis by a third party such as a support provider, using a trace file allows you to capture events and send the trace file to the support provider for analysis.
• Saving the captured data to a trace table allows you to use the query processing tools in SQL Server to access the data, or to view the data in SQL Server Profiler. However, only members of the sysadmin fixed server role or the table creator can access the trace table directly.

Important To capture event data to a SQL Server table, the Application Name data column must be captured within the trace.

Each time the trace is run, captured data is appended to the trace file or trace table.

Important Capturing trace data to a table is slower than capturing to a file. An alternative is to capture a trace to a file, open the trace file, and then save the trace as a trace table.

When using a trace file, SQL Server Profiler saves captured event data (not trace definitions) to a SQLServerProfiler (*.trc) file. The extension is added to the end of the file automatically when the trace file is saved, regardless of any other specified extension. For example, if you specify a trace file called Trace.dat, the file created is called Trace.dat.trc.

Saving the captured event data to the Windows NT application log allows the event data to be viewed using the Windows NT Event Viewer. The Windows NT application log is likely to be used to store captured event data when you are specifically monitoring errors and warnings. For example, when monitoring the OLE DB Errors or Execution Warnings event classes, it can be desirable to record these errors in the same location as other Windows NT application errors.

Forwarding the events captured in a trace to a forwarding server is useful when you want to centrally monitor event data generated by many computers running SQL Server. To forward events, a queue is created on each computer running SQL Server that you want to monitor. Each queue is configured using xp_trace_setqueuedestination to send the events to the forwarding server. By monitoring only the forwarding server using SQL Server Profiler or another consumer, you can effectively monitor all the events sent from the computers running SQL Server. The name of the computer that generated the event is attached as a prefix to the event.

Saving Trace Definitions

The definition of a trace includes the event classes, data columns, event criteria (filters), and all other properties (except the captured event data) used to create a trace. Trace definitions created using SQL Server Profiler are automatically saved in the registry on the computer running SQL Server Profiler. Trace (queue) definitions created using extended stored procedures are stored in the registry of the computer running SQL Server where the queue was created.

Saved trace definitions can be used as templates for traces. For example, if you frequently monitor SQL Server to analyze performance by capturing the same event data each time, using the same trace definition allows you to monitor the same events without having to define the event classes, data columns, and so on every time you create a trace. Additionally, a trace definition created using SQL Server Profiler can be given to another user to monitor specific SQL Server events. For example, a support provider can supply a customer with a trace definition that can be used to monitor specific events relating to a problem the customer is experiencing. The trace definition is used by the customer to capture the required event data, which is then sent to the support provider for analysis.

To save a trace definition so that it can be used on another computer, export the trace definition to a file using SQL Server Profiler. Trace definitions are stored in trace definition (*.tdf) files. The trace definition can then be imported from the file to another computer using SQL Server Profiler.

Note In order to start an imported trace, the server name specified may need to be changed.

To save a trace to a file

To save a trace to a table

To save a trace to the Windows NT application log

To forward event data to another server

To save a trace (queue) definition

To export a trace definition

To import a trace definition