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

Before you execute the server-side trace, you need to open the server-side trace file to make minor
modifications. Within the top portion of the script, you need to define a time for the trace to stop itself,
and provide a new file name for the trace to save its output data using the following code:
-- Create a Queue
declare @rc int
declare @TraceID int
declare @maxfilesize bigint
declare @DateTime datetime
set @DateTime = '2007-05-12 10:22:14.000'
set @maxfilesize = 5
-- Please replace the text InsertFileNameHere, with an appropriate
-- filename prefixed by a path, e.g., c: \ MyFolder \ MyTrace. The .trc extension
-- will be appended to the filename automatically. If you are writing from
-- remote server to local drive, please use UNC path and make sure server has
-- write access to your network share
exec @rc = sp_trace_create @TraceID output, 0, N'InsertFileNameHere', @maxfile-
size, @Datetime
if (@rc != 0) goto error
-- Client side File and Table cannot be scripted
Assuming the trace will be started at '2007-05-14 09:00:00.000' for a 15-minute trace, you need to
change the @DateTime to '2007-05-14 09:15:00.000' as shown here:
set @DateTime = '2007-05-14 09:15:00.000'
For the file name you substitute the N'InsertFileNameHere' with a legitimate file name, for example,
c: \ temp \ myServerSideTraceOutput_CostlyQueries . This file name has to be unique.
The default file extension is .trc .
The second input parameter was 0, which you will change to 2. The value 2 indicates that the output file
is a rollover file. It also specifies that when the maximum_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 this new file.) The new file
will have the same name as the previous file, but an integer will be appended to indicate its sequence.
You also added the last parameter file count and specified a value of 10. This indicates that the maximum
number of rollover files is 10.
exec @rc = sp_trace_create @TraceID output,
2,
N'c: \ temp \ myServerSideTraceOutput_CostlyQueries',
@maxfilesize,
@Datetime,
10