| if object_id('dbo.configureServerSideTrace', 'P') is not null
drop procedure dbo.configureServerSideTrace
go
-- Example for:
-- Start Trace
-- declare @traceID int
-- exec dbo.configureServerSideTrace @traceStatus = 1
-- ,@traceID = @traceID output
-- ,@maxFileSize = 10000
-- ,@traceFileName = N'e:\VMITrace\Undo'
-- ,@spId = @@spid
--
-- End Trace
-- exec dbo.configureServerSideTrace @traceStatus = 0, @traceID = @traceID
create procedure dbo.configureServerSideTrace
(@traceStatus bit -- 1 => Start Trace
-- 0 => Stop Trace
,@traceID int output -- If the Trace is started, this param will return the TraceID
-- For stopping the trace, the param has to be provided
,@spId int = null -- provide the @@spid, if you want to filter only events for this conection
-- Optional. If not provided => no filter. Not needed for stopping the trace
,@maxFileSize bigint = 5000 -- Maximum Trace File Size in Megabyte. Trace will be stopped, if the filesize is reached.
,@traceFileName nvarchar(200) = null -- Name of the trace file (server side!)
-- Optional. Not neded for stoping the trace
-- Attention! If the file already exists, the SP will yield an error
-- and no trace is started.
) as
begin
if (@traceStatus = 0)
begin
exec sp_trace_setstatus @TraceID, 0
exec sp_trace_setstatus @TraceID, 2
return;
end
-- Create a Queue
declare @rc int
exec @rc = sp_trace_create @TraceID output, 0, @traceFileName, @maxfilesize, NULL
if (@rc != 0) goto error
-- Set the events
declare @on bit
set @on = 1
exec sp_trace_setevent @TraceID, 43, 15, @on
exec sp_trace_setevent @TraceID, 43, 48, @on
exec sp_trace_setevent @TraceID, 43, 1, @on
exec sp_trace_setevent @TraceID, 43, 34, @on
exec sp_trace_setevent @TraceID, 43, 35, @on
exec sp_trace_setevent @TraceID, 43, 51, @on
exec sp_trace_setevent @TraceID, 43, 4, @on
exec sp_trace_setevent @TraceID, 43, 12, @on
exec sp_trace_setevent @TraceID, 43, 13, @on
exec sp_trace_setevent @TraceID, 43, 14, @on
exec sp_trace_setevent @TraceID, 43, 22, @on
exec sp_trace_setevent @TraceID, 42, 1, @on
exec sp_trace_setevent @TraceID, 42, 14, @on
exec sp_trace_setevent @TraceID, 42, 22, @on
exec sp_trace_setevent @TraceID, 42, 34, @on
exec sp_trace_setevent @TraceID, 42, 35, @on
exec sp_trace_setevent @TraceID, 42, 51, @on
exec sp_trace_setevent @TraceID, 42, 4, @on
exec sp_trace_setevent @TraceID, 42, 12, @on
exec sp_trace_setevent @TraceID, 45, 16, @on
exec sp_trace_setevent @TraceID, 45, 48, @on
exec sp_trace_setevent @TraceID, 45, 1, @on
exec sp_trace_setevent @TraceID, 45, 17, @on
exec sp_trace_setevent @TraceID, 45, 18, @on
exec sp_trace_setevent @TraceID, 45, 34, @on
exec sp_trace_setevent @TraceID, 45, 35, @on
exec sp_trace_setevent @TraceID, 45, 51, @on
exec sp_trace_setevent @TraceID, 45, 4, @on
exec sp_trace_setevent @TraceID, 45, 12, @on
exec sp_trace_setevent @TraceID, 45, 13, @on
exec sp_trace_setevent @TraceID, 45, 14, @on
exec sp_trace_setevent @TraceID, 45, 22, @on
exec sp_trace_setevent @TraceID, 45, 15, @on
-- XML Statistics Profile
exec sp_trace_setevent @TraceID, 146, 1, @on
exec sp_trace_setevent @TraceID, 146, 51, @on
exec sp_trace_setevent @TraceID, 146, 4, @on
exec sp_trace_setevent @TraceID, 146, 12, @on
-- Filter: Log only events for the provided @@spid
if @spId is not null
exec sp_trace_setfilter @TraceID, 12, 0, 0, @spID
-- Set the trace status to start
exec sp_trace_setstatus @TraceID, 1
goto finish
error:
select ErrorCode=@rc
finish:
end
go |
