I want to setup a server side replay trace and scripted out setting up the trace. However I want the trace to end either after 12 hours or once 10 1GB files have been created. Normally with a max file count, the system deletes the oldest file and then creates the new one. In my situation this would end with me having Files 2-11 instead of the desired 1-10. I have space limitations and need to replay all the steps from start to finish. I could just set the file size limit to 10GB. I think there is a 1GB limit on file size, but this could just be a limit in the GUI.

Any ideas on how I can stop the script once it reaches 10 files?

declare @rc int
declare @TraceID int
declare @maxfilesize bigint
declare @DateTime datetime
declare @maxfilecount int

set @DateTime = DATEADD(HOUR,12,GETDATE())
set @maxfilesize = 1024
set @maxfilecount = 10

exec @rc = sp_trace_create @TraceID output, 2, N'D:\Output\Trace', @maxfilesize, @Datetime, @maxfilecount 
  • Just to clarify, part of why I don't want to use a single file is that when a trace file hits it size limit and rollover is off then SQL throws an error. This is not a desired outcome
    – Tim J
    Oct 22 '15 at 12:45

I figured out a way to do it.

declare @files table (ID int IDENTITY, FileName varchar(100))
insert into @files execute xp_cmdshell 'dir d:\Output\*.trc /b'

Checking the file count and if it is at my limit then I lookup the trace id and use sp_trace_setstatus to stop it. Since my trace files take a while to get up to the filesize limit, I run this job every 5 or 10 minutes on agent.


There is no easy way to STOP trace when certain no. of files are reached. Instead you can delete the older one and keep the no. of files in the limit that you defined using @filecount parameter.

Refer BOL - sp_trace_create - You have to use @filecount = 'max_rollover_files' along with @options = 2 -- specifies TRACE_FILE_ROLLOVER

Specifies the maximum number or trace files to be maintained with the same base filename. max_rollover_files is int, greater than one. This parameter is valid only if the TRACE_FILE_ROLLOVER option is specified. When max_rollover_files is specified, SQL Server tries to maintain no more than max_rollover_files trace files by deleting the oldest trace file before opening a new trace file. SQL Server tracks the age of trace files by appending a number to the base file name.


I think there is a 1GB limit on file size, but this could just be a limit in the GUI.

Server side trace does not have a limit of 1GB. I have 5GB trace files created in my environment every hour.

  • Yes I know the max file count will delete the oldest file once it reaches the limit. The goal is to have it stop not delete the delete the oldest file since this is going to be a replay trace file. If I lose all the inserts, updates, creates, or alters in the first file, then subsequent queries that may call for the new or changed data would or give different results. That is why I want to stop the trace so I don't lose the early files and I don't use up all my free space.
    – Tim J
    Oct 22 '15 at 8:17

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.