SQL Server - Stopping a server side trace

Question

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 

Answer 1

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.

Answer 2

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.