Script existing server side trace to create a new trace with same definition

  • Article

I wrote this script today and thought of sharing since it might be useful for you. I wrote it because there is no option to set rollover of a existing server side trace and we have lost the original script which created this server side trace in SQL Server. So idea is to script existing trace, drop it and create a new trace with rollover option set.

-- Create a Queue

declare @rc int

declare @TraceID int

declare @maxfilesize bigint

set @maxfilesize = 5 -- Size in MB

-- Please replace the text InsertFileNameHere, with an appropriate

-- filename prefixed by a path, e.g., c:\MyFolder\MyTrace. The .trc extension

-- will be appended to the filename automatically.

exec @rc = sp_trace_create @TraceID output, 2, N'InsertFileNameHere', @maxfilesize, NULL -- 2 here specifies that this trace will rollover

if (@rc != 0) goto error

-- Set the events

declare @on bit

set @on = 1

-- Get the output of the query below and replace the query below with the output generated

SELECT 'exec sp_trace_setevent @TraceID, ' + cast(eventid as varchar(50)) + ',' + cast(columnid as varchar(50)) + ', @on'

FROM fn_trace_geteventinfo(x)

-- Set the Filters

declare @intfilter int

declare @bigintfilter bigint

-- You need to get the output of /***SELECT * FROM fn_trace_getfilterinfo (x)***/ and update the query below accordingly

exec sp_trace_setfilter @TraceID, 10, 0, 7, N'SQL Server Profiler'

-- 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