How to Create a Trace With Profiler in SQL Server 2008

Traces allow you to track the specific actions performed against a SQL Server database. They provide valuable information for troubleshooting database errors and tuning database engine performance. We show you how to create a trace using SQL Server 2008 and earlier.

How to Create a Trace With SQL Server Profiler

Use SQL Server Management Studio to create a trace.

1. Open SQL Server Management Studio by selecting it from the Start menu.

2. From the Tools menu, choose SQL Server Profiler.

3. When SQL Server Profiler opens, select New Trace from the File menu.

4. SQL Server Profiler prompts you to connect to the SQL Server instance you wish to profile. Provide the connection details and click Connect to continue.

5. Create a descriptive name for your trace and type it into the Trace Name textbox.

6. Select a template for your trace from the drop-down menu.

7. Select Save to File to save your trace to a file on the local hard drive. Provide a file name and location in the Save As window.

8. Click the Events Selection tab to review the events you may monitor with your trace. Some events will automatically be selected based on the template you chose although you're free to modify those defaults. You may view additional options by clicking the Show All Events and Show All Columns checkboxes.

9. Click the Run button to begin your trace. SQL Server creates the trace. When you are finished, select Stop Trace from the File menu.

Template Tips

The Standard template collects a variety of information about SQL Server connections, stored procedures, and Transact-SQL statements.

The Tuning template collects information that may be used with the Database Engine Tuning Advisor to tune your SQL Server's performance.

The TSQL_Replay template gathers enough information about each Transact-SQL statement to recreate the activity in the future. This template is useful to reconstruct queries to assess, for example, for inappropriate data access.