Server Side SQL Tracing

this is a sample script I used for capturing SQL trace on SQL 2000. higher versions have similliar statements and methods.



create procedure usp_PerfTrace @stoptime datetime = null
as
begin
declare @traceId int, @ret int
declare @tOn bit
--,@stoptime datetime,
declare @fileSize bigint,@tFile nvarchar(128)
set @tOn=1
--set @stoptime='2012-06-01 01:00'
if (@stoptime is null)
set @stoptime=dateadd(hh,1,getdate())
set @fileSize = 5
set @tFile = N'c:\shared\SQLTrace\perfTrace'+convert(varchar,getdate(),102)+'.'+convert(varchar,datepart(hh,getdate()))+convert(varchar,datepart(mm,getdate()))
--set trace file rollover after 5mb
--select N'c:\shared\SQLTrace\perfTrace'+convert(varchar,getdate(),102)+'.'+convert(varchar,datepart(hh,getdate()))+convert(varchar,datepart(mm,getdate()))

exec @ret=sp_trace_create @traceid =  @traceId OUTPUT  , @options =  2 ,
@tracefile = @tFile ,@maxfilesize=@fileSize, @stoptime=@stoptime

SELECT RET=@ret, TraceID = @traceId
exec sp_trace_setevent @traceId, 10, 1, @tOn
exec sp_trace_setevent @traceId, 10, 2, @tOn
exec sp_trace_setevent @traceId, 10, 3, @tOn
exec sp_trace_setevent @traceId, 10, 12, @tOn
exec sp_trace_setevent @traceId, 10, 13, @tOn
exec sp_trace_setevent @traceId, 10, 14, @tOn
exec sp_trace_setevent @traceId, 10, 15, @tOn
exec sp_trace_setevent @traceId, 10, 16, @tOn
exec sp_trace_setevent @traceId, 10, 17, @tOn
exec sp_trace_setevent @traceId, 10, 18, @tOn
exec sp_trace_setevent @traceId, 10, 27, @tOn
exec sp_trace_setevent @traceId, 12, 1, @tOn
exec sp_trace_setevent @traceId, 12, 2, @tOn
exec sp_trace_setevent @traceId, 12, 3, @tOn
exec sp_trace_setevent @traceId, 12, 12, @tOn
exec sp_trace_setevent @traceId, 12, 13, @tOn
exec sp_trace_setevent @traceId, 12, 14, @tOn
exec sp_trace_setevent @traceId, 12, 15, @tOn
exec sp_trace_setevent @traceId, 12, 16, @tOn
exec sp_trace_setevent @traceId, 12, 17, @tOn
exec sp_trace_setevent @traceId, 12, 18, @tOn
exec sp_trace_setevent @traceId, 12, 27, @tOn
exec sp_trace_setevent @traceId, 41, 1, @tOn
exec sp_trace_setevent @traceId, 41, 2, @tOn
exec sp_trace_setevent @traceId, 41, 3, @tOn
exec sp_trace_setevent @traceId, 41, 12, @tOn
exec sp_trace_setevent @traceId, 41, 13, @tOn
exec sp_trace_setevent @traceId, 41, 14, @tOn
exec sp_trace_setevent @traceId, 41, 15, @tOn
exec sp_trace_setevent @traceId, 41, 16, @tOn
exec sp_trace_setevent @traceId, 41, 17, @tOn
exec sp_trace_setevent @traceId, 41, 18, @tOn
exec sp_trace_setevent @traceId, 41, 27, @tOn
exec sp_trace_setevent @traceId, 43, 1, @tOn
exec sp_trace_setevent @traceId, 43, 2, @tOn
exec sp_trace_setevent @traceId, 43, 3, @tOn
exec sp_trace_setevent @traceId, 43, 12, @tOn
exec sp_trace_setevent @traceId, 43, 13, @tOn
exec sp_trace_setevent @traceId, 43, 14, @tOn
exec sp_trace_setevent @traceId, 43, 15, @tOn
exec sp_trace_setevent @traceId, 43, 16, @tOn
exec sp_trace_setevent @traceId, 43, 17, @tOn
exec sp_trace_setevent @traceId, 43, 18, @tOn
exec sp_trace_setevent @traceId, 43, 27, @tOn
exec sp_trace_setevent @traceId, 45, 1, @tOn
exec sp_trace_setevent @traceId, 45, 2, @tOn
exec sp_trace_setevent @traceId, 45, 3, @tOn
exec sp_trace_setevent @traceId, 45, 12, @tOn
exec sp_trace_setevent @traceId, 45, 13, @tOn
exec sp_trace_setevent @traceId, 45, 14, @tOn
exec sp_trace_setevent @traceId, 45, 15, @tOn
exec sp_trace_setevent @traceId, 45, 16, @tOn
exec sp_trace_setevent @traceId, 45, 17, @tOn
exec sp_trace_setevent @traceId, 45, 18, @tOn
exec sp_trace_setevent @traceId, 45, 27, @tOn

--column 13=duration, comparison 4= ">=", 1 is 1 milisecond
declare @value bigint
select @value=1
exec sp_trace_setfilter @traceid = @traceId, @columnid=13,@logical_operator= 0, @comparison_operator =4, @value=@value
--0 is equal
exec sp_trace_setfilter @traceid = @traceId, @columnid=3,@logical_operator= 0, @comparison_operator =0, @value=7
--no like  sql profiler
exec sp_trace_setfilter @traceId, 10, 0, 7, N'SQL Profiler'

--satus: 0 stop,1 start, 2 close and delete its definition from server
exec sp_trace_setstatus @traceId, 1

end
exec usp_PerfTrace

--exec sp_trace_setstatus 2, 0
--exec sp_trace_setstatus 2, 2
--go
--exec sp_trace_getdata @traceId, 0
--go

in SQL 2000, these functions can be used to return trace information.

SELECT * FROM ::fn_trace_getinfo(default)

SELECT * into xxx200 FROM ::fn_trace_gettable('c:\shared\SQLTrace\Stress20010301100.trc', default)

in SQL 2008, fn_get_tabledata, fn_trace_getinfo can be used instead.