Finding Problem Queries with SQL Profiler - Professional SQL Server 2005 Performance Tuning - page 133

The script begins by declaring the needed variables. The variable @rc is declared as a return code for
error handling purposes. The variable @TraceID will be used to store a number assigned by a new trace.

The variable @DateTime specifies the stop time of the trace. In this code, it is initialized at '2007-05-14
09:15:00.000' .Thevariable @maxfilesize specifies the maximum size in megabytes (MB) a trace file
can grow; it is initialized at a value of 5.

-- Create a Queue
declare @rc int
declare @TraceID int
declare @maxfilesize bigint
set @maxfilesize = 5

set @DateTime = '2007-05-14 09:15:00.000'
set @maxfilesize = 5

The next program statement is to create a new trace. The store procedure sp_trace_create creates a new
trace definition. There are several input and output parameters followed by the stored procedure:

exec @rc = sp_trace_create @TraceID output,
2,
N'c: \ temp \ myServerSideTraceOutput_CostlyQueries',
@maxfilesize,
@Datetime,
10

❑ @TraceID output: An output returned by sp_trace_create . This trace identification number

will be used by other stored procedures in the script. It is used to identify a specific SQL Trace.

❑ 2: This corresponds to the second parameter, @options . The value 2 specifies that when the

max_file_size is reached, the current trace file will be closed and a new file will be created. All
new records will be written to the new file. The new file will have the same name as the previous
file, but an integer will be appended to indicate its sequence. For example, if the original trace file
is named filename.trc , the next trace file will be named filename_1.trc , and following that
trace file will be filename_2.trc ,andsoon.

❑ N'c: \ temp \ myServerSideTraceOutput_CostlyQueries': This corresponds to the @trace_file

parameter. The trace file can be either a local directory (such as N 'C:\MSSQL\Trace\trace.trc')
or a UNC path (Uniform Naming Convention) to a shared drive (N'\Servername\Sharename\
Directory\trace.trc'). A local directory means that it resides on a monitored server.

❑ @maxfilesize: The fourth parameter specifies the maximum size in megabytes (MB) a trace file

can grow. In the beginning of the script, it was initialized at 5 (set @maxfilesize = 5 ).

❑ @Datetime: This corresponds to the fifth input parameter, @stoptime . How does a trace stop

time work? If NULL is specified, the trace will run until it is manually stopped or until the server
shuts down. If both stop_time and max_file_size are specified, and TRACE_FILE_ROLLOVER
is not specified, the trace will stop when either the specified stop time or maximum file size is
reached. If stop_time, max_file_size ,and TRACE_FILE_ROLLOVER are all specified, the trace
will stop at the specified stop time, assuming the trace does not fill up the drive first.

❑ 10: This corresponds to the last input parameter max_rollover_filers .Itspecifiesthemaxi-

mum number or trace files to be maintained with the same base file name.