Although SQL Trace is marked started it’s deprecation stage, it will be around for quite some time to come. Therefore I thought I would share a couple of changes I make to server side traces when I use them. (It also means I will always have somewhere to find the code without writing it from scratch!)
In the past when you scripted a service side trace in SQL Profiler, the script generated would contain a comment containing the statements required to stop and close the trace.
This may have been removed because you needed to change the script and put in the correct TraceID parameters, therefore limiting the value of the suggested code. If it fits your circumstances (i.e. no other Server Side traces being run on the system) you could use the following generic code to stop the most recent trace.~
DECLARE @TraceID int ;
SET @TraceID = ( SELECT MAX(traceid)/
FROM sys.fn_trace_getinfo(0) ) ;
SELECT @TraceID ;
IF @TraceID IS NOT NULL
BEGIN
EXEC sp_trace_setstatus @TraceID, 0
EXEC sp_trace_setstatus @TraceID, 2
END ;
GO
If you are using the Default Trace then you may get the following message if you try to stop it.
Msg 19070, Level 16, State 1, Procedure sp_trace_setstatus, Line 1
The default trace cannot be stopped or modified. Use SP_CONFIGURE to turn it off.
Therefore you may want to amend the above script to avoid the trace with a TraceID of 1.
DECLARE @TraceID int ;
SET @TraceID = ( SELECT MAX(traceid)
FROM sys.fn_trace_getinfo(DEFAULT)
WHERE traceid > 1 ) ;
SELECT @TraceID ;
IF @TraceID IS NOT NULL
BEGIN
EXEC sp_trace_setstatus @TraceID, 0 ;
EXEC sp_trace_setstatus @TraceID, 2 ;
END ;
GO
An alternate method to identify the Trace to stop could be to identify a unique part of the filename. Filename is property 2 returned by fn_trace_getinfo.
DECLARE @TraceID int ;
SET @TraceID = ( SELECT traceid
FROM sys.fn_trace_getinfo(DEFAULT)
WHERE Property = 2
AND CAST(VALUE as varchar(max)) LIKE 'T:\TraceFiles\Trace_%' )
SELECT @TraceID ;
IF @TraceID IS NOT NULL
BEGIN
EXEC sp_trace_setstatus @TraceID, 0 ;
EXEC sp_trace_setstatus @TraceID, 2 ;
END ;
GO
Ideally the trace files will be places on a drive on the server that is not going to cause resource conflicts when writing to any of the database files. When you script the trace definition the filename is one of the things you have to enter yourself. The command that is scripted is:
exec @rc = sp_trace_create @TraceID output, 0, N'InsertFileNameHere', @maxfilesize, NULL
If you hard code this to be a specific file then the trace will fail to start if the file already exists e.g.
With the following line in your script:
exec @rc = sp_trace_create @TraceID output, 0, N'T:\TraceFiles\Trace_Test', @maxfilesize, NULL
If you run the trace script a second time you will see the error:
Windows error occurred while running SP_TRACE_CREATE. Error = 0x80070050(failed to retrieve text for this error. Reason: 15100).
Msg 19062, Level 16, State 1, Procedure sp_trace_create, Line 1
Could not create a trace file.
To overcome this you can make the file unique by adding a date and time.
DECLARE @tracefilename nvarchar(245) ;
SET @tracefilename = N'T:\TraceFiles\Trace_' + REPLACE(REPLACE(REPLACE(CONVERT(varchar(19), GETDATE(), 120 ),' ','_'),':',''),'-','') ;
EXEC @rc = sp_trace_create @TraceID output, 0, @tracefilename, @maxfilesize, NULL ;~
The scripted value for @maxfilesize is a paltry 5MB. You may want to change this to be a little bigger, especially as the default options (second parameter is 0) does not to use trace file rollover.
Anyone who has looked up what the fifth parameter is (set to NULL) will know this is a stop time. If you know how long the script should run, you will not need to stop the trace yourself if you set the parameter to an absolute date and time.
DECLARE @tracefilename nvarchar(245) ;
DECLARE @stoptime datetime ;
DECLARE @timeintervalsecs int ;
SET @timeintervalsecs = 600 ; /* 10 minutes */
SET @stoptime = DATEADD( ss, @timeintervalsecs, getdate() ) ;
SET @tracefilename = N'T:\TraceFiles\Trace_' + REPLACE(REPLACE(REPLACE(CONVERT(varchar(19), GETDATE(), 120 ),' ','_'),':',''),'-','') ;
EXEC @rc = sp_trace_create @TraceID output, 0, @tracefilename, @maxfilesize, @stoptime ;
if (@rc != 0) goto error ;
Whilst writing this article I have noticed a “bug” with the Default Profiler Template, it seems to have always been present and noone has bothered to log it. To see it go to http://tinyurl.com/DefaultProfilerTraceBug*