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 |