SQL Server Profiler   «Prev  Next»

Lesson3 Traces
ObjectiveCreate a trace.

Create Trace and Filters using SQL-Server

When you are creating a trace, you follow these steps:
  1. Create the trace by picking what events you want to track, the information you want, the way you want the data filtered, and where you want the trace stored.
  2. Run the trace.
  3. Replay the trace.
  4. Analyze the results.

The first step in monitoring SQL Server with SQL Server Profiler is to create a trace and filters. This will allow you to choose what gets monitored. When defining a trace you can specify the following items:
  1. A name for the trace
  2. Whether or not the trace is runable by other users
  3. The event classes that you want to trace
  4. The information to track for the events
  5. How to filter the events
  6. Where to store the traced information

When filtering events, you can pick any data column and assign values to include and values to exclude. In most cases, you will want to make your filter as restricted as possible, otherwise you will see lots of events traced. This can make it harder to troubleshoot problems.

Creating a Trace using SQL-Server Profiler

To create a trace, select File->New-> Trace from inside SQL Server Profile. This will bring up the Trace Property screen. The following series of images below will illustrate the features of this screen.

Defining a trace

Flipbook image 1
1) The General properties tab allows you to define a trace name, trace type, the SQL Server to save the trace on, and where to save the output.

Flipbook image 2
2) The Events tab allows you to define what events to trace. Trace Properties

Flipbook image 3
3) "The Data Column tabs allows you to define what information to trace. Trace Properties"

Flipbook image 4
4) The Filter tab allows you to enter values for the different data columns. These values define when an event is traced and when an event is ignored. Trace Properties

Trace tips
  1. Every trace must have a name. SQL Server profiler will not let you exit the Trace Property screen without naming the trace.
  2. You can modify a trace using the same set of screens that you use to create a trace.
  3. If you do modify a trace, changes will not take affect until the next time you run the trace.
  4. SQL Server Profiler will not run a trace on the stored procedures that are used to maintain security information.
In the next lesson, you will learn about the type of events you can monitor using tracing.

SEMrush Software