HiveBrain v1.2.0
Get Started
← Back to all entries
patternsqlMinor

SQL Server - Stopping a server side trace

Submitted by: @import:stackexchange-dba··
0
Viewed 0 times
stoppingsqlsideservertrace

Problem

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

Solution

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.

Code Snippets

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

Context

StackExchange Database Administrators Q#118818, answer score: 2

Revisions (0)

No revisions yet.