Problem using SP_TRACE_SETFILTER - it doesn't seem to apply the fi

Warning - this is quite a LONG post.
Hi all,
Anyone got experience using SP_TRACE_SETFILTER? Let me paint the picture - I
want to do some tracing in our production environment, and have jobs set up
to automaGically run the traces, so I am building the traces in an SP, via
SP_TRACE sp's. This main sp (UP_Auto_Profiler_Perf) can then get called from
SQL scheduled jobs, and will happily create the trace files. I have another
SP to then copy these files to another server, and automaGically import them
in some tables for analyzing.
All good and well, so far, EXCEPT that while my trace get's created with no
errors, when I look at the trace data, it seems that the filters are not
being applied. When I open it up from Profiler, I can see the trace
properties, and the Events and Data columns are all correct, but there are no
Filters. Anyone see where I am going wrong?
So, here's the code FEEL FREE TO SUGGEST IMPROVEMENTS
The SP
if exists (select 1 from sysobjects where name =
'Up_Auto_Profiler_Perf')drop procedure Up_Auto_Profiler_Perfgocreate
procedure Up_Auto_Profiler_Perf @.TraceFile nvarchar(128),--Nvarchar as
required by SP_CREATE_TRACE. The File to be created.@.StopTime
datetime,--The time to stop the trace.@.MaxSize BigInt = 10,--BigInt as
required by SP_CREATE_TRACE. The maximum size per
file.@.DurationFilterValueBIGINT = 0, --The duration to filter on (in
ms).@.TraceID int OUTPUT--Create By:Regan Galbraith--Create
On:2004-12-28--Purpose:--This stored procedure was written to facilitate
the creation of profiler traces that write to files.--Example:--exec
Up_Auto_Profiler_Perf
'C:\TraceFiles\AutoProf_SP_20041228_1000','2004-12-28-10:05:00.000',100,500--Possible
future additions:--1> simple enhancement to specify DB to store data, and
table.--2>accepting a parameter instead of apply the default .trc. Use the
..trc as default--3>implementing default value's for dir's, so that it can
run without parm's ... good or bad...-Change Control:version 1 - Regan
Galbraith 2004-12-28--Creation and adding of comment--version 1.2 -
Regan Galbraith 2004-12-29--Added @.DurationFilterValue logic to allow
generation of limited data, --filtering on duration--Added Output
parametre @.TracId to return for lookup on trace.--AS--Declare Control
Variabledeclare @.ReturnCode Int--Declare Option Variables declare @.Option
intdeclare @.EventId intdeclare @.On bitdeclare @.Value intdeclare
@.ComparisonOperator intdeclare @.ColumnId intdeclare @.LogicalOperator
int--Set Option Variableset @.Option = 2--TraceFileRollOver --Specifies
that when the max_file_size is reached, the current trace file is closed and
a new file is created.--Set Trace Filter to exclude System Ids - that is
ObjectId > 100set @.Value = 100 set @.ColumnId = 22--0bjectidset
@.LogicalOperator = 0--and (1 = OR)set @.ComparisonOperator = 2--Greater
than--Set Control Variablesset @.ReturnCode = 0--No Errorset @.On =
1--True--Create a trace, retrieve @.TraceId exec sp_trace_create @.TraceId
output,@.Option,@.TraceFile,@.MaxSize,@.StopTimeselect @.ReturnCode=@.@.Errorif
@.ReturnCode <> 0Beginif @.ReturnCode = 1Print 'Error 1 - Unknown
error.'if @.ReturnCode = 10Print 'Error 10 - Invalid options. Returned when
options specified are incompatible.'if @.ReturnCode = 12Print 'Error 12 -
Cannot create tracefile - check if file already exists, or this trace already
running'if @.ReturnCode = 13Print 'Error 13 - Out of memory. Returned when
there is not enough memory to perform the specified action.'if @.ReturnCode =
14Print 'Error 14 - Invalid stop time. Returned when the stop time
specified has already happened.'if @.ReturnCode = 15Print 'Error 15 -
Invalid parameters. Returned when the user supplied incompatible
parameters.'elsePrint 'Unexpected and Unknown error In creating trace -
Please review'Goto ErrorHandlerEnd--Populate Trace with Events--SECTION
CURSORS-- this set is : cursor executeset @.EventId = 74exec sp_trace_setevent
@.TraceId,@.EventId,27,@.On--EventClassexec sp_trace_setevent
@.TraceId,@.EventId,25,@.On--IntegerData-- this set is : Cursor Openset
@.EventId = 53exec sp_trace_setevent @.TraceId,@.EventId,27,@.On--EventClassexec
sp_trace_setevent @.TraceId,@.EventId,25,@.On--IntegerData-- this set is :
Cursor Recompileset @.EventId = 75exec sp_trace_setevent
@.TraceId,@.EventId,27,@.On--EventClass--SECTION ERRORS AND WARNINGS-- this set
is : MissingJoinPredicateset @.EventId = 80exec sp_trace_setevent
@.TraceId,@.EventId,27,@.On--EventClass--SECTION LOCKS -- this set is :
Lock:DeadLockset @.EventId = 25exec sp_trace_setevent
@.TraceId,@.EventId,2,@.On--BinaryDataexec sp_trace_setevent
@.TraceId,@.EventId,13,@.On--durationexec sp_trace_setevent
@.TraceId,@.EventId,15,@.On--EndTimeexec sp_trace_setevent
@.TraceId,@.EventId,27,@.On--EventClassexec sp_trace_setevent
@.TraceId,@.EventId,24,@.On--indexIDexec sp_trace_setevent
@.TraceId,@.EventId,25,@.On--IntegerDataexec sp_trace_setevent
@.TraceId,@.EventId,32,@.On--Modeexec sp_trace_setevent
@.TraceId,@.EventId,22,@.On--ObjectID-- this set is : Lock:DeadLockChainset
@.EventId = 59exec sp_trace_setevent @.TraceId,@.EventId,2,@.On--BinaryDataexec
sp_trace_setevent @.TraceId,@.EventId,27,@.On--EventClassexec sp_trace_setevent
@.TraceId,@.EventId,24,@.On--indexIDexec sp_trace_setevent
@.TraceId,@.EventId,25,@.On--IntegerDataexec sp_trace_setevent
@.TraceId,@.EventId,32,@.On--Modeexec sp_trace_setevent
@.TraceId,@.EventId,22,@.On--ObjectID-- this set is : Lock:Timeoutset @.EventId
= 27exec sp_trace_setevent @.TraceId,@.EventId,2,@.On--BinaryDataexec
sp_trace_setevent @.TraceId,@.EventId,13,@.On--durationexec sp_trace_setevent
@.TraceId,@.EventId,15,@.On--EndTimeexec sp_trace_setevent
@.TraceId,@.EventId,27,@.On--EventClassexec sp_trace_setevent
@.TraceId,@.EventId,24,@.On--indexIDexec sp_trace_setevent
@.TraceId,@.EventId,32,@.On--Modeexec sp_trace_setevent
@.TraceId,@.EventId,22,@.On--ObjectID--SECTION PERFORMANCE-- this set is :
Execution Plan-- set @.EventId = 68-- exec sp_trace_setevent
@.TraceId,@.EventId,2,@.On--BinaryData-- exec sp_trace_setevent
@.TraceId,@.EventId,27,@.On--EventClass-- exec sp_trace_setevent
@.TraceId,@.EventId,25,@.On--IntegerData-- exec sp_trace_setevent
@.TraceId,@.EventId,22,@.On--ObjectID-- exec sp_trace_setevent
@.TraceId,@.EventId,1,@.On--TextData-- this set is : Show Planset @.EventId =
97exec sp_trace_setevent @.TraceId,@.EventId,2,@.On--BinaryDataexec
sp_trace_setevent @.TraceId,@.EventId,27,@.On--EventClassexec sp_trace_setevent
@.TraceId,@.EventId,25,@.On--IntegerDataexec sp_trace_setevent
@.TraceId,@.EventId,1,@.On--TextData-- --this set is : Show Plan Statistics--
set @.EventId = 98-- exec sp_trace_setevent
@.TraceId,@.EventId,2,@.On--BinaryData-- exec sp_trace_setevent
@.TraceId,@.EventId,27,@.On--EventClass-- exec sp_trace_setevent
@.TraceId,@.EventId,25,@.On--IntegerData-- exec sp_trace_setevent
@.TraceId,@.EventId,1,@.On--TextData-- -- --this set is : Show Plan Text-- set
@.EventId = 96-- exec sp_trace_setevent @.TraceId,@.EventId,2,@.On--BinaryData--
exec sp_trace_setevent @.TraceId,@.EventId,27,@.On--EventClass-- exec
sp_trace_setevent @.TraceId,@.EventId,25,@.On--IntegerData-- exec
sp_trace_setevent @.TraceId,@.EventId,1,@.On--TextData--SECTION STORED
PROCEDURES--this set is SP:Recompileset @.EventId = 37exec sp_trace_setevent
@.TraceId,@.EventId,27,@.On--EventClassexec sp_trace_setevent
@.TraceId,@.EventId,29,@.On--NestLevelexec sp_trace_setevent
@.TraceId,@.EventId,22,@.On--ObjectIDexec sp_trace_setevent
@.TraceId,@.EventId,34,@.On--ObjectNameexec sp_trace_setevent
@.TraceId,@.EventId,1,@.On--TextData--this set is SP:RPC:Completedset @.EventId
= 10exec sp_trace_setevent @.TraceId,@.EventId,27,@.On--EventClassexec
sp_trace_setevent @.TraceId,@.EventId,13,@.On--durationexec sp_trace_setevent
@.TraceId,@.EventId,18,@.On--cpuexec sp_trace_setevent
@.TraceId,@.EventId,15,@.On--EndTimeexec sp_trace_setevent
@.TraceId,@.EventId,16,@.On--Readsexec sp_trace_setevent
@.TraceId,@.EventId,1,@.On--TextDataexec sp_trace_setevent
@.TraceId,@.EventId,17,@.On--Writes--SECTION TRANSACTIONS--this set is : SQL
Transaction-- set @.EventId = 50-- exec sp_trace_setevent
@.TraceId,@.EventId,13,@.On--duration-- exec sp_trace_setevent
@.TraceId,@.EventId,15,@.On--EndTime-- exec sp_trace_setevent
@.TraceId,@.EventId,27,@.On--EventClass-- exec sp_trace_setevent
@.TraceId,@.EventId,21,@.On--EventSubClass-- exec sp_trace_setevent
@.TraceId,@.EventId,34,@.On--ObjectName-- exec sp_trace_setevent
@.TraceId,@.EventId,1,@.On--TextData-- exec sp_trace_setevent
@.TraceId,@.EventId,4,@.On--TRansactionID--SECTION TSQL--this set is : SQL
BatchCompletedset @.EventId = 12exec sp_trace_setevent
@.TraceId,@.EventId,18,@.On--cpuexec sp_trace_setevent
@.TraceId,@.EventId,13,@.On--durationexec sp_trace_setevent
@.TraceId,@.EventId,15,@.On--EndTimeexec sp_trace_setevent
@.TraceId,@.EventId,27,@.On--EventClassexec sp_trace_setevent
@.TraceId,@.EventId,16,@.On--Readsexec sp_trace_setevent
@.TraceId,@.EventId,1,@.On--TextDataexec sp_trace_setevent
@.TraceId,@.EventId,17,@.On--Writes-- --this set is : SQL:StmtCompleted-- set
@.EventId = 41-- exec sp_trace_setevent @.TraceId,@.EventId,18,@.On--cpu-- exec
sp_trace_setevent @.TraceId,@.EventId,13,@.On--duration-- exec
sp_trace_setevent @.TraceId,@.EventId,15,@.On--EndTime-- exec sp_trace_setevent
@.TraceId,@.EventId,27,@.On--EventClass-- exec sp_trace_setevent
@.TraceId,@.EventId,25,@.On--IntegerData-- exec sp_trace_setevent
@.TraceId,@.EventId,29,@.On--NestLevel-- exec sp_trace_setevent
@.TraceId,@.EventId,22,@.On--ObjectID-- exec sp_trace_setevent
@.TraceId,@.EventId,16,@.On--Reads-- exec sp_trace_setevent
@.TraceId,@.EventId,1,@.On--TextData-- exec sp_trace_setevent
@.TraceId,@.EventId,17,@.On--Writesexec sp_trace_setstatus @.TraceId,1select
@.ReturnCode=@.@.Errorif @.ReturnCode <> 0Beginif @.ReturnCode = 13Print
'ERROR 13 - Out of memory. Returned when there is not enough memory to
perform the specified action.'elseif @.ReturnCode = 9Print 'ERROR 9 -
The specified Trace Handle is not valid.'elseif @.ReturnCode = 8print
'ERROR 8 - The specified Status is not valid.'elsePrint 'ERROR 1 - Unknown
Error'GoTo ErrorHandlerendexec sp_trace_setfilter
@.TraceId,@.ColumnId,@.LogicalOperator,@.ComparisonOpe rator,@.Valueselect
@.ReturnCode=@.@.Errorif @.ReturnCode <> 0Beginif @.ReturnCode = 1print 'ERROR
1 - Unknown error.'elseif @.ReturnCode = 2Print 'ERROR 2 - The trace is
currently running. Changing the trace at this time will result in an
error.'elseif @.ReturnCode = 4Print 'ERROR 4 - The specified Column is not
valid.'elseif @.ReturnCode = 5print 'ERROR 5 - The specified Column is not
allowed for filtering.'elseif @.ReturnCode = 6print 'ERROR 6 - The
specified Comparison Operator is not valid. 'elseif @.ReturnCode = 7print
'ERROR 7 - The specified Logical Operator is not valid.'elseif @.ReturnCode
= 9print 'ERROR 9 - The specified Trace Handle is not valid.'elseif
@.ReturnCode = 13print 'ERROR 13 - Out of memory. Returned when there is not
enough memory to perform the specified action.'elseif @.ReturnCode =
16print 'ERROR 16 - The function is not valid for this trace.'elsePrint
'ERROR x - Unknown Error'GoTo ErrorHandlerendif @.DurationFilterValue <>
0beginset @.columnId = 13 --Durationset @.ComparisonOperator = 2
--Greater thanexec sp_trace_setfilter
@.TraceId,@.ColumnId,@.LogicalOperator,@.ComparisonOpe rator,@.DurationFilterValueselect
@.ReturnCode=@.@.Errorif @.ReturnCode <> 0Beginif @.ReturnCode = 1print
'ERROR 1 - Unknown error.'elseif @.ReturnCode = 2Print 'ERROR 2 - The
trace is currently running. Changing the trace at this time will result in an
error.'elseif @.ReturnCode = 4Print 'ERROR 4 - The specified Column is
not valid.'elseif @.ReturnCode = 5print 'ERROR 5 - The specified Column
is not allowed for filtering.'elseif @.ReturnCode = 6print 'ERROR 6 -
The specified Comparison Operator is not valid. 'elseif @.ReturnCode =
7print 'ERROR 7 - The specified Logical Operator is not valid.'elseif
@.ReturnCode = 9print 'ERROR 9 - The specified Trace Handle is not
valid.'elseif @.ReturnCode = 13print 'ERROR 13 - Out of memory.
Returned when there is not enough memory to perform the specified
action.'elseif @.ReturnCode = 16print 'ERROR 16 - The function is not
valid for this trace.'elsePrint 'ERROR x - Unknown Error'GoTo
ErrorHandlerendendErrorHandler:Return @.ReturnCode
The Code that calls the trace
DECLARE @.FileName NVARCHAR(128)DECLARE @.RunStopTime DATETIMEDECLARE
@.MaxSizeBIGINTDECLARE @.DurationFilterBIGINTDECLARE
@.TraceIDINTEGERDECLARE @.MinutesToRunINTEGERDECLARE @.RCINTEGERDECLARE
@.DBID INTEGERDECLARE @.DBNAME NVARCHAR(128)-- Set Error VariablesSET
@.DBNAME = DB_NAME()SET @.DBID = DB_ID()SET @.rc = 0--Set Control Variablesset
@.MinutesToRun = 5 -- this is how long the trace will run forset @.MaxSize =
100 -- this is the maximum size for a file, in MBset @.DurationFilter =
1000--Set Running Variables--the time the trace will stopset @.RunStopTime =
dateadd(mi,@.MinutesToRun,getdate())--the file to be create - full name (not
UNC)set @.FileName =
'C:\Auto_Prof\AutoProf_SP_'+cast(datepart(yyyy,getdate()) as
char(4))--Years+right(cast(datepart(m ,getdate())+100 as
char(3)),2)--Months+cast(datepart(d,getdate()) as
char(2))+'_'--Days+right(cast(datepart(hh,getdate())+100 as
char(3)),2)--Hours+right(cast(datepart(mi,getdate())+100 as
char(3)),2)--Minutes--Display variablesprint 'File created is :
'+@.FileNameprint 'End time will be : '+cast(@.RunStopTime as
varchar(20))--Create trace, writing out to tracefile, until endtimeexec
@.Rc=Up_Auto_Profiler_Perf
@.FileName,@.RunStopTime,@.MaxSize,@.DurationFilter,@.T raceID output--Error
Handlingselect @.rcselect @.TraceIDSELECT * FROM
::fn_trace_getfilterinfo(@.TraceID)if (@.rc <> 0 )RAISERROR ('Create/Run of
Trace FAILED', 16, 1, @.DBID, @.DBNAME)GO
Ta...
the data type of the @.durationfiltervalue must correspond with the data type
of the column to be filtered. otherwise it will not work.
unfortunately I was unable to post this into a QA session my connection kept
crashing .
Hope that helps
"Wanderer" wrote:

> Warning - this is quite a LONG post.
> Hi all,
> Anyone got experience using SP_TRACE_SETFILTER? Let me paint the picture - I
> want to do some tracing in our production environment, and have jobs set up
> to automaGically run the traces, so I am building the traces in an SP, via
> SP_TRACE sp's. This main sp (UP_Auto_Profiler_Perf) can then get called from
> SQL scheduled jobs, and will happily create the trace files. I have another
> SP to then copy these files to another server, and automaGically import them
> in some tables for analyzing.
> All good and well, so far, EXCEPT that while my trace get's created with no
> errors, when I look at the trace data, it seems that the filters are not
> being applied. When I open it up from Profiler, I can see the trace
> properties, and the Events and Data columns are all correct, but there are no
> Filters. Anyone see where I am going wrong?
> So, here's the code FEEL FREE TO SUGGEST IMPROVEMENTS
> The SP
> if exists (select 1 from sysobjects where name =
> 'Up_Auto_Profiler_Perf')drop procedure Up_Auto_Profiler_Perfgocreate
> procedure Up_Auto_Profiler_Perf @.TraceFile nvarchar(128),--Nvarchar as
> required by SP_CREATE_TRACE. The File to be created.@.StopTime
> datetime,--The time to stop the trace.@.MaxSize BigInt = 10,--BigInt as
> required by SP_CREATE_TRACE. The maximum size per
> file.@.DurationFilterValueBIGINT = 0, --The duration to filter on (in
> ms).@.TraceID int OUTPUT--Create By:Regan Galbraith--Create
> On:2004-12-28--Purpose:--This stored procedure was written to facilitate
> the creation of profiler traces that write to files.--Example:--exec
> Up_Auto_Profiler_Perf
> 'C:\TraceFiles\AutoProf_SP_20041228_1000','2004-12-28-10:05:00.000',100,500--Possible
> future additions:--1> simple enhancement to specify DB to store data, and
> table.--2>accepting a parameter instead of apply the default .trc. Use the
> .trc as default--3>implementing default value's for dir's, so that it can
> run without parm's ... good or bad...-Change Control:version 1 - Regan
> Galbraith 2004-12-28--Creation and adding of comment--version 1.2 -
> Regan Galbraith 2004-12-29--Added @.DurationFilterValue logic to allow
> generation of limited data, --filtering on duration--Added Output
> parametre @.TracId to return for lookup on trace.--AS--Declare Control
> Variabledeclare @.ReturnCode Int--Declare Option Variables declare @.Option
> intdeclare @.EventId intdeclare @.On bitdeclare @.Value intdeclare
> @.ComparisonOperator intdeclare @.ColumnId intdeclare @.LogicalOperator
> int--Set Option Variableset @.Option = 2--TraceFileRollOver --Specifies
> that when the max_file_size is reached, the current trace file is closed and
> a new file is created.--Set Trace Filter to exclude System Ids - that is
> ObjectId > 100set @.Value = 100 set @.ColumnId = 22--0bjectidset
> @.LogicalOperator = 0--and (1 = OR)set @.ComparisonOperator = 2--Greater
> than--Set Control Variablesset @.ReturnCode = 0--No Errorset @.On =
> 1--True--Create a trace, retrieve @.TraceId exec sp_trace_create @.TraceId
> output,@.Option,@.TraceFile,@.MaxSize,@.StopTimeselect @.ReturnCode=@.@.Errorif
> @.ReturnCode <> 0Beginif @.ReturnCode = 1Print 'Error 1 - Unknown
> error.'if @.ReturnCode = 10Print 'Error 10 - Invalid options. Returned when
> options specified are incompatible.'if @.ReturnCode = 12Print 'Error 12 -
> Cannot create tracefile - check if file already exists, or this trace already
> running'if @.ReturnCode = 13Print 'Error 13 - Out of memory. Returned when
> there is not enough memory to perform the specified action.'if @.ReturnCode =
> 14Print 'Error 14 - Invalid stop time. Returned when the stop time
> specified has already happened.'if @.ReturnCode = 15Print 'Error 15 -
> Invalid parameters. Returned when the user supplied incompatible
> parameters.'elsePrint 'Unexpected and Unknown error In creating trace -
> Please review'Goto ErrorHandlerEnd--Populate Trace with Events--SECTION
> CURSORS-- this set is : cursor executeset @.EventId = 74exec sp_trace_setevent
> @.TraceId,@.EventId,27,@.On--EventClassexec sp_trace_setevent
> @.TraceId,@.EventId,25,@.On--IntegerData-- this set is : Cursor Openset
> @.EventId = 53exec sp_trace_setevent @.TraceId,@.EventId,27,@.On--EventClassexec
> sp_trace_setevent @.TraceId,@.EventId,25,@.On--IntegerData-- this set is :
> Cursor Recompileset @.EventId = 75exec sp_trace_setevent
> @.TraceId,@.EventId,27,@.On--EventClass--SECTION ERRORS AND WARNINGS-- this set
> is : MissingJoinPredicateset @.EventId = 80exec sp_trace_setevent
> @.TraceId,@.EventId,27,@.On--EventClass--SECTION LOCKS -- this set is :
> Lock:DeadLockset @.EventId = 25exec sp_trace_setevent
> @.TraceId,@.EventId,2,@.On--BinaryDataexec sp_trace_setevent
> @.TraceId,@.EventId,13,@.On--durationexec sp_trace_setevent
> @.TraceId,@.EventId,15,@.On--EndTimeexec sp_trace_setevent
> @.TraceId,@.EventId,27,@.On--EventClassexec sp_trace_setevent
> @.TraceId,@.EventId,24,@.On--indexIDexec sp_trace_setevent
> @.TraceId,@.EventId,25,@.On--IntegerDataexec sp_trace_setevent
> @.TraceId,@.EventId,32,@.On--Modeexec sp_trace_setevent
> @.TraceId,@.EventId,22,@.On--ObjectID-- this set is : Lock:DeadLockChainset
> @.EventId = 59exec sp_trace_setevent @.TraceId,@.EventId,2,@.On--BinaryDataexec
> sp_trace_setevent @.TraceId,@.EventId,27,@.On--EventClassexec sp_trace_setevent
> @.TraceId,@.EventId,24,@.On--indexIDexec sp_trace_setevent
> @.TraceId,@.EventId,25,@.On--IntegerDataexec sp_trace_setevent
> @.TraceId,@.EventId,32,@.On--Modeexec sp_trace_setevent
> @.TraceId,@.EventId,22,@.On--ObjectID-- this set is : Lock:Timeoutset @.EventId
> = 27exec sp_trace_setevent @.TraceId,@.EventId,2,@.On--BinaryDataexec
> sp_trace_setevent @.TraceId,@.EventId,13,@.On--durationexec sp_trace_setevent
> @.TraceId,@.EventId,15,@.On--EndTimeexec sp_trace_setevent
> @.TraceId,@.EventId,27,@.On--EventClassexec sp_trace_setevent
> @.TraceId,@.EventId,24,@.On--indexIDexec sp_trace_setevent
> @.TraceId,@.EventId,32,@.On--Modeexec sp_trace_setevent
> @.TraceId,@.EventId,22,@.On--ObjectID--SECTION PERFORMANCE-- this set is :
> Execution Plan-- set @.EventId = 68-- exec sp_trace_setevent
> @.TraceId,@.EventId,2,@.On--BinaryData-- exec sp_trace_setevent
> @.TraceId,@.EventId,27,@.On--EventClass-- exec sp_trace_setevent
> @.TraceId,@.EventId,25,@.On--IntegerData-- exec sp_trace_setevent
> @.TraceId,@.EventId,22,@.On--ObjectID-- exec sp_trace_setevent
> @.TraceId,@.EventId,1,@.On--TextData-- this set is : Show Planset @.EventId =
> 97exec sp_trace_setevent @.TraceId,@.EventId,2,@.On--BinaryDataexec
> sp_trace_setevent @.TraceId,@.EventId,27,@.On--EventClassexec sp_trace_setevent
> @.TraceId,@.EventId,25,@.On--IntegerDataexec sp_trace_setevent
> @.TraceId,@.EventId,1,@.On--TextData-- --this set is : Show Plan Statistics--
> set @.EventId = 98-- exec sp_trace_setevent
> @.TraceId,@.EventId,2,@.On--BinaryData-- exec sp_trace_setevent
> @.TraceId,@.EventId,27,@.On--EventClass-- exec sp_trace_setevent
> @.TraceId,@.EventId,25,@.On--IntegerData-- exec sp_trace_setevent
> @.TraceId,@.EventId,1,@.On--TextData-- -- --this set is : Show Plan Text-- set
> @.EventId = 96-- exec sp_trace_setevent @.TraceId,@.EventId,2,@.On--BinaryData--
> exec sp_trace_setevent @.TraceId,@.EventId,27,@.On--EventClass-- exec
> sp_trace_setevent @.TraceId,@.EventId,25,@.On--IntegerData-- exec
> sp_trace_setevent @.TraceId,@.EventId,1,@.On--TextData--SECTION STORED
> PROCEDURES--this set is SP:Recompileset @.EventId = 37exec sp_trace_setevent
> @.TraceId,@.EventId,27,@.On--EventClassexec sp_trace_setevent
> @.TraceId,@.EventId,29,@.On--NestLevelexec sp_trace_setevent
> @.TraceId,@.EventId,22,@.On--ObjectIDexec sp_trace_setevent
> @.TraceId,@.EventId,34,@.On--ObjectNameexec sp_trace_setevent
> @.TraceId,@.EventId,1,@.On--TextData--this set is SP:RPC:Completedset @.EventId
> = 10exec sp_trace_setevent @.TraceId,@.EventId,27,@.On--EventClassexec
> sp_trace_setevent @.TraceId,@.EventId,13,@.On--durationexec sp_trace_setevent
> @.TraceId,@.EventId,18,@.On--cpuexec sp_trace_setevent
> @.TraceId,@.EventId,15,@.On--EndTimeexec sp_trace_setevent
> @.TraceId,@.EventId,16,@.On--Readsexec sp_trace_setevent
> @.TraceId,@.EventId,1,@.On--TextDataexec sp_trace_setevent
> @.TraceId,@.EventId,17,@.On--Writes--SECTION TRANSACTIONS--this set is : SQL
> Transaction-- set @.EventId = 50-- exec sp_trace_setevent
> @.TraceId,@.EventId,13,@.On--duration-- exec sp_trace_setevent
> @.TraceId,@.EventId,15,@.On--EndTime-- exec sp_trace_setevent
> @.TraceId,@.EventId,27,@.On--EventClass-- exec sp_trace_setevent
> @.TraceId,@.EventId,21,@.On--EventSubClass-- exec sp_trace_setevent
> @.TraceId,@.EventId,34,@.On--ObjectName-- exec sp_trace_setevent
> @.TraceId,@.EventId,1,@.On--TextData-- exec sp_trace_setevent
> @.TraceId,@.EventId,4,@.On--TRansactionID--SECTION TSQL--this set is : SQL
> BatchCompletedset @.EventId = 12exec sp_trace_setevent
> @.TraceId,@.EventId,18,@.On--cpuexec sp_trace_setevent
> @.TraceId,@.EventId,13,@.On--durationexec sp_trace_setevent
> @.TraceId,@.EventId,15,@.On--EndTimeexec sp_trace_setevent
> @.TraceId,@.EventId,27,@.On--EventClassexec sp_trace_setevent
> @.TraceId,@.EventId,16,@.On--Readsexec sp_trace_setevent
> @.TraceId,@.EventId,1,@.On--TextDataexec sp_trace_setevent
> @.TraceId,@.EventId,17,@.On--Writes-- --this set is : SQL:StmtCompleted-- set
> @.EventId = 41-- exec sp_trace_setevent @.TraceId,@.EventId,18,@.On--cpu-- exec
> sp_trace_setevent @.TraceId,@.EventId,13,@.On--duration-- exec
> sp_trace_setevent @.TraceId,@.EventId,15,@.On--EndTime-- exec sp_trace_setevent
> @.TraceId,@.EventId,27,@.On--EventClass-- exec sp_trace_setevent
> @.TraceId,@.EventId,25,@.On--IntegerData-- exec sp_trace_setevent
> @.TraceId,@.EventId,29,@.On--NestLevel-- exec sp_trace_setevent
> @.TraceId,@.EventId,22,@.On--ObjectID-- exec sp_trace_setevent
> @.TraceId,@.EventId,16,@.On--Reads-- exec sp_trace_setevent
> @.TraceId,@.EventId,1,@.On--TextData-- exec sp_trace_setevent
> @.TraceId,@.EventId,17,@.On--Writesexec sp_trace_setstatus @.TraceId,1select
> @.ReturnCode=@.@.Errorif @.ReturnCode <> 0Beginif @.ReturnCode = 13Print
> 'ERROR 13 - Out of memory. Returned when there is not enough memory to
> perform the specified action.'elseif @.ReturnCode = 9Print 'ERROR 9 -
> The specified Trace Handle is not valid.'elseif @.ReturnCode = 8print
> 'ERROR 8 - The specified Status is not valid.'elsePrint 'ERROR 1 - Unknown
> Error'GoTo ErrorHandlerendexec sp_trace_setfilter
> @.TraceId,@.ColumnId,@.LogicalOperator,@.ComparisonOpe rator,@.Valueselect
> @.ReturnCode=@.@.Errorif @.ReturnCode <> 0Beginif @.ReturnCode = 1print 'ERROR
> 1 - Unknown error.'elseif @.ReturnCode = 2Print 'ERROR 2 - The trace is
> currently running. Changing the trace at this time will result in an
> error.'elseif @.ReturnCode = 4Print 'ERROR 4 - The specified Column is not
> valid.'elseif @.ReturnCode = 5print 'ERROR 5 - The specified Column is not
> allowed for filtering.'elseif @.ReturnCode = 6print 'ERROR 6 - The
> specified Comparison Operator is not valid. 'elseif @.ReturnCode = 7print
> 'ERROR 7 - The specified Logical Operator is not valid.'elseif @.ReturnCode
> = 9print 'ERROR 9 - The specified Trace Handle is not valid.'elseif
> @.ReturnCode = 13print 'ERROR 13 - Out of memory. Returned when there is not
> enough memory to perform the specified action.'elseif @.ReturnCode =
> 16print 'ERROR 16 - The function is not valid for this trace.'elsePrint
> 'ERROR x - Unknown Error'GoTo ErrorHandlerendif @.DurationFilterValue <>
> 0beginset @.columnId = 13 --Durationset @.ComparisonOperator = 2
> --Greater thanexec sp_trace_setfilter
> @.TraceId,@.ColumnId,@.LogicalOperator,@.ComparisonOpe rator,@.DurationFilterValueselect
> @.ReturnCode=@.@.Errorif @.ReturnCode <> 0Beginif @.ReturnCode = 1print
> 'ERROR 1 - Unknown error.'elseif @.ReturnCode = 2Print 'ERROR 2 - The
> trace is currently running. Changing the trace at this time will result in an
> error.'elseif @.ReturnCode = 4Print 'ERROR 4 - The specified Column is
> not valid.'elseif @.ReturnCode = 5print 'ERROR 5 - The specified Column
> is not allowed for filtering.'elseif @.ReturnCode = 6print 'ERROR 6 -
> The specified Comparison Operator is not valid. 'elseif @.ReturnCode =
> 7print 'ERROR 7 - The specified Logical Operator is not valid.'elseif
> @.ReturnCode = 9print 'ERROR 9 - The specified Trace Handle is not
> valid.'elseif @.ReturnCode = 13print 'ERROR 13 - Out of memory.
> Returned when there is not enough memory to perform the specified
> action.'elseif @.ReturnCode = 16print 'ERROR 16 - The function is not
> valid for this trace.'elsePrint 'ERROR x - Unknown Error'GoTo
> ErrorHandlerendendErrorHandler:Return @.ReturnCode
>
> The Code that calls the trace
> DECLARE @.FileName NVARCHAR(128)DECLARE @.RunStopTime DATETIMEDECLARE
> @.MaxSizeBIGINTDECLARE @.DurationFilterBIGINTDECLARE
> @.TraceIDINTEGERDECLARE @.MinutesToRunINTEGERDECLARE @.RCINTEGERDECLARE
> @.DBID INTEGERDECLARE @.DBNAME NVARCHAR(128)-- Set Error VariablesSET
> @.DBNAME = DB_NAME()SET @.DBID = DB_ID()SET @.rc = 0--Set Control Variablesset
> @.MinutesToRun = 5 -- this is how long the trace will run forset @.MaxSize =
> 100 -- this is the maximum size for a file, in MBset @.DurationFilter =
> 1000--Set Running Variables--the time the trace will stopset @.RunStopTime =
> dateadd(mi,@.MinutesToRun,getdate())--the file to be create - full name (not
> UNC)set @.FileName =
> 'C:\Auto_Prof\AutoProf_SP_'+cast(datepart(yyyy,getdate()) as
> char(4))--Years+right(cast(datepart(m ,getdate())+100 as
> char(3)),2)--Months+cast(datepart(d,getdate()) as
> char(2))+'_'--Days+right(cast(datepart(hh,getdate())+100 as
> char(3)),2)--Hours+right(cast(datepart(mi,getdate())+100 as
> char(3)),2)--Minutes--Display variablesprint 'File created is :
> '+@.FileNameprint 'End time will be : '+cast(@.RunStopTime as
> varchar(20))--Create trace, writing out to tracefile, until endtimeexec
> @.Rc=Up_Auto_Profiler_Perf
> @.FileName,@.RunStopTime,@.MaxSize,@.DurationFilter,@.T raceID output--Error
> Handlingselect @.rcselect @.TraceIDSELECT * FROM
> ::fn_trace_getfilterinfo(@.TraceID)if (@.rc <> 0 )RAISERROR ('Create/Run of
> Trace FAILED', 16, 1, @.DBID, @.DBNAME)GO
>
> Ta...
>