Saving Traces and Trace Templates
It is important to distinguish saving trace files from saving trace templates. Saving a trace file involves saving the captured event data to a specified place. Saving a trace template involves saving the definition of the trace, such as specified data columns, event classes, or filters.
Saving Traces
Save captured event data to a file or a SQL Server table when you need to analyze or replay the captured data at a later time. Use a trace file to do the following:
- Use a trace file or trace table to create a workload that is used as input for Database Engine Tuning Advisor.
- Use a trace file to capture events and send the trace file to the support provider for analysis.
- Use the query processing tools in SQL Server to access the data or to view the data in SQL Server Profiler. Only members of the sysadmin fixed server role or the table creator can access the trace table directly.
Note
Capturing trace data to a table is a slower operation than capturing trace data to a file. An alternative is to capture trace data to a file, open the trace file, and then save the trace as a trace table.
When you use a trace file, SQL Server Profiler saves captured event data (not trace definitions) to a SQL Server Profiler Trace (*.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 Templates
The template definition of a trace includes the event classes, data columns, filters, and all other properties (except the captured event data) that are used to create a trace. SQL Server Profiler provides predefined system templates for common tracing tasks and for specific tasks, such as creating a workload that Database Engine Tuning Advisor can use to tune the physical database design. You can also create and save user-defined templates.
Importing and Exporting Templates
SQL Server Profiler allows you to import and export templates from one server to another. Exporting a template moves a copy of an existing template to a directory that you specify. Importing a template makes a copy of a template that you specify. When these templates are viewed in SQL Server Profiler, you can distinguish them from system templates by the term "(user)" that follows the template name. You cannot overwrite or directly modify a predefined system template.
Analyzing Performance with Templates
If you frequently monitor SQL Server, use templates to analyze performance. The templates capture the same event data each time and use the same trace definition to monitor the same events. You do not need to define the event classes and data columns every time you create a trace. Also, a template can be given to another user to monitor specific SQL Server events. For example, a support provider can supply a customer with a template. The customer uses the template to capture the required event data, which is then sent to the support provider for analysis.
To save a trace to a file
How to: Save Trace Results to a File (SQL Server Profiler)
sp_trace_create (Transact-SQL)
See Also
Tasks
How to: Save Trace Results to a Table (SQL Server Profiler)
How to: Create a Trace Template (SQL Server Profiler)
How to: Derive a Template from a Running Trace (SQL Server Profiler)
How to: Derive a Template from a Trace File or Trace Table (SQL Server Profiler)
How to: Export a Trace Template (SQL Server Profiler)
How to: Import a Trace Template (SQL Server Profiler)
Other Resources
Tuning the Physical Database Design