Today I learned…
How to set up a recurring Server-side SQL trace that runs every hour for 10 minutes.
Issues:
- 6 people in the room are staring at me waiting for the last second request to be done at the end of an 11 hour day (3 of them from the VBV – Very Big Vendor)
- Trace file names must be different, or you get errors
- Trace files cannot end with a number
- I can’t tell time when I am hungry and tired
I know I need to script out the trace to T-SQL (which I had already done), and put this in a ‘start trace’ job on an hourly schedule. I have set these up before to run ad hoc, but not recurring.
I also need code to stop the trace 10 minutes later that my boss understands…so we are going for fast and functional here….not pretty.
In the SQL script Profiler generates, the @tracefile parameter supplied to sp_trace_create is specified in the call:
exec @rc = sp_trace_create @TraceID output, 2, 'D:\MyTraceFiles\Trace', @maxfilesize, NULL
If you need different names for each execution you have to get creative. I created a parameter to feed that included a getdate():
declare @tracefile nvarchar(245) set @tracefile = 'D:\MyTraceFiles\Trace_'+Cast (getdate() as varchar(20))+'_Trace'
this worked and gave this result in testing:
D:\MyTraceFiles\Trace_Dec 8 2016 7:08AM_Trace
But…running the full trace script generated files in the directory with this name:
Trace_Dec 8 2016 7
It took me some time playing with variable sizes and such before I asked one of the VBV guys to look at it. We figured out the ‘:’ in the time stamp was an issue, so I included a REPLACE:
set @tracefile = 'D:\MyTraceFiles\Trace_'+replace(Cast (getdate() as varchar(20)),':','')+'_Trace'
Which gave this result:
D:\MyTraceFiles\Trace_Dec 8 2016 711AM_Trace
More importantly, I had a functioning trace script I could schedule:
-- Create a Queue declare @rc int declare @TraceID int declare @maxfilesize bigint declare @tracefile nvarchar(245) set @maxfilesize = 4096 set @tracefile = 'D:\MyTraceFiles\Trace_'+replace(Cast (getdate() as varchar(20)),':','')+'_Trace' --select @tracefile exec @rc = sp_trace_create @TraceID output, 2, @tracefile, @maxfilesize, NULL if (@rc != 0) goto error -- Client side File and Table cannot be scripted -- Set the events declare @on bit set @on = 1 exec sp_trace_setevent @TraceID, 10, 1, @on exec sp_trace_setevent @TraceID, 10, 9, @on exec sp_trace_setevent @TraceID, 10, 2, @on . . . exec sp_trace_setevent @TraceID, 13, 64, @on exec sp_trace_setevent @TraceID, 13, 66, @on -- Set the Filters declare @intfilter int declare @bigintfilter bigint exec sp_trace_setfilter @TraceID, 35, 0, 6, N'Database1' exec sp_trace_setfilter @TraceID, 35, 1, 6, N'Database2' --above, the '1' in parameter 3 changes this to an OR instead of an AND -- so where databasename is Database1 OR Database2. Also new info for ---me today -- Set the trace status to start exec sp_trace_setstatus @TraceID, 1 -- display trace id for future references select TraceID=@TraceID goto finish error: select ErrorCode=@rc finish: go
Scheduling was simple…drop the above into Job ‘Start trace’ and enable to run every hour on the hour.
Create a second job to run every hour on the 10 minutes with the following:
exec sp_trace_setstatus 2, 0 exec sp_trace_setstatus 2, 2
On this system, my trace will always be 2. 1 is the default trace and nobody else here runs them.
Summary:
I already knew I don’t do well at the end of a long day or in a hurry, so I took my time while the others waited, in order to not put in a bad solution to run overnight. There are much more elegant ways to do this, but this worked for me and I learned it on the fly.
Thanks for reading!