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. @.DurationFilterValue BIGINT = 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 <> 0 Begin if @.ReturnCode = 1 Print 'Error 1 - Unknown
error.' if @.ReturnCode = 10 Print 'Error 10 - Invalid options. Returned when
options specified are incompatible.' if @.ReturnCode = 12 Print 'Error 12 -
Cannot create tracefile - check if file already exists, or this trace already
running' if @.ReturnCode = 13 Print 'Error 13 - Out of memory. Returned when
there is not enough memory to perform the specified action.' if @.ReturnCode = 14 Print 'Error 14 - Invalid stop time. Returned when the stop time
specified has already happened.' if @.ReturnCode = 15 Print 'Error 15 -
Invalid parameters. Returned when the user supplied incompatible
parameters.' else Print '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 <> 0 Begin if @.ReturnCode = 13 Print
'ERROR 13 - Out of memory. Returned when there is not enough memory to
perform the specified action.' else if @.ReturnCode = 9 Print 'ERROR 9 -
The specified Trace Handle is not valid.' else if @.ReturnCode = 8 print
'ERROR 8 - The specified Status is not valid.' else Print 'ERROR 1 - Unknown
Error' GoTo ErrorHandlerendexec sp_trace_setfilter
@.TraceId,@.ColumnId,@.LogicalOperator,@.ComparisonOperator,@.Valueselect
@.ReturnCode=@.@.Errorif @.ReturnCode <> 0 Begin if @.ReturnCode = 1 print 'ERROR
1 - Unknown error.' else if @.ReturnCode = 2 Print 'ERROR 2 - The trace is
currently running. Changing the trace at this time will result in an
error.' else if @.ReturnCode = 4 Print 'ERROR 4 - The specified Column is not
valid.' else if @.ReturnCode = 5 print 'ERROR 5 - The specified Column is not
allowed for filtering.' else if @.ReturnCode = 6 print 'ERROR 6 - The
specified Comparison Operator is not valid. ' else if @.ReturnCode = 7 print
'ERROR 7 - The specified Logical Operator is not valid.' else if @.ReturnCode
= 9 print 'ERROR 9 - The specified Trace Handle is not valid.' else if
@.ReturnCode = 13 print 'ERROR 13 - Out of memory. Returned when there is not
enough memory to perform the specified action.' else if @.ReturnCode = 16 print 'ERROR 16 - The function is not valid for this trace.' else Print
'ERROR x - Unknown Error' GoTo ErrorHandlerendif @.DurationFilterValue <>
0 begin set @.columnId = 13 --Duration set @.ComparisonOperator = 2
--Greater than exec sp_trace_setfilter
@.TraceId,@.ColumnId,@.LogicalOperator,@.ComparisonOperator,@.DurationFilterValue select
@.ReturnCode=@.@.Error if @.ReturnCode <> 0 Begin if @.ReturnCode = 1 print
'ERROR 1 - Unknown error.' else if @.ReturnCode = 2 Print 'ERROR 2 - The
trace is currently running. Changing the trace at this time will result in an
error.' else if @.ReturnCode = 4 Print 'ERROR 4 - The specified Column is
not valid.' else if @.ReturnCode = 5 print 'ERROR 5 - The specified Column
is not allowed for filtering.' else if @.ReturnCode = 6 print 'ERROR 6 -
The specified Comparison Operator is not valid. ' else if @.ReturnCode = 7 print 'ERROR 7 - The specified Logical Operator is not valid.' else if
@.ReturnCode = 9 print 'ERROR 9 - The specified Trace Handle is not
valid.' else if @.ReturnCode = 13 print 'ERROR 13 - Out of memory.
Returned when there is not enough memory to perform the specified
action.' else if @.ReturnCode = 16 print 'ERROR 16 - The function is not
valid for this trace.' else Print 'ERROR x - Unknown Error' GoTo
ErrorHandler endendErrorHandler:Return @.ReturnCode
The Code that calls the trace
DECLARE @.FileName NVARCHAR(128)DECLARE @.RunStopTime DATETIMEDECLARE
@.MaxSize BIGINTDECLARE @.DurationFilter BIGINTDECLARE
@.TraceID INTEGERDECLARE @.MinutesToRun INTEGERDECLARE @.RC INTEGERDECLARE
@.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,@.TraceID 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. @.DurationFilterValue BIGINT = 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 <> 0 Begin if @.ReturnCode = 1 Print 'Error 1 - Unknown
> error.' if @.ReturnCode = 10 Print 'Error 10 - Invalid options. Returned when
> options specified are incompatible.' if @.ReturnCode = 12 Print 'Error 12 -
> Cannot create tracefile - check if file already exists, or this trace already
> running' if @.ReturnCode = 13 Print 'Error 13 - Out of memory. Returned when
> there is not enough memory to perform the specified action.' if @.ReturnCode => 14 Print 'Error 14 - Invalid stop time. Returned when the stop time
> specified has already happened.' if @.ReturnCode = 15 Print 'Error 15 -
> Invalid parameters. Returned when the user supplied incompatible
> parameters.' else Print '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 <> 0 Begin if @.ReturnCode = 13 Print
> 'ERROR 13 - Out of memory. Returned when there is not enough memory to
> perform the specified action.' else if @.ReturnCode = 9 Print 'ERROR 9 -
> The specified Trace Handle is not valid.' else if @.ReturnCode = 8 print
> 'ERROR 8 - The specified Status is not valid.' else Print 'ERROR 1 - Unknown
> Error' GoTo ErrorHandlerendexec sp_trace_setfilter
> @.TraceId,@.ColumnId,@.LogicalOperator,@.ComparisonOperator,@.Valueselect
> @.ReturnCode=@.@.Errorif @.ReturnCode <> 0 Begin if @.ReturnCode = 1 print 'ERROR
> 1 - Unknown error.' else if @.ReturnCode = 2 Print 'ERROR 2 - The trace is
> currently running. Changing the trace at this time will result in an
> error.' else if @.ReturnCode = 4 Print 'ERROR 4 - The specified Column is not
> valid.' else if @.ReturnCode = 5 print 'ERROR 5 - The specified Column is not
> allowed for filtering.' else if @.ReturnCode = 6 print 'ERROR 6 - The
> specified Comparison Operator is not valid. ' else if @.ReturnCode = 7 print
> 'ERROR 7 - The specified Logical Operator is not valid.' else if @.ReturnCode
> = 9 print 'ERROR 9 - The specified Trace Handle is not valid.' else if
> @.ReturnCode = 13 print 'ERROR 13 - Out of memory. Returned when there is not
> enough memory to perform the specified action.' else if @.ReturnCode => 16 print 'ERROR 16 - The function is not valid for this trace.' else Print
> 'ERROR x - Unknown Error' GoTo ErrorHandlerendif @.DurationFilterValue <>
> 0 begin set @.columnId = 13 --Duration set @.ComparisonOperator = 2
> --Greater than exec sp_trace_setfilter
> @.TraceId,@.ColumnId,@.LogicalOperator,@.ComparisonOperator,@.DurationFilterValue select
> @.ReturnCode=@.@.Error if @.ReturnCode <> 0 Begin if @.ReturnCode = 1 print
> 'ERROR 1 - Unknown error.' else if @.ReturnCode = 2 Print 'ERROR 2 - The
> trace is currently running. Changing the trace at this time will result in an
> error.' else if @.ReturnCode = 4 Print 'ERROR 4 - The specified Column is
> not valid.' else if @.ReturnCode = 5 print 'ERROR 5 - The specified Column
> is not allowed for filtering.' else if @.ReturnCode = 6 print 'ERROR 6 -
> The specified Comparison Operator is not valid. ' else if @.ReturnCode => 7 print 'ERROR 7 - The specified Logical Operator is not valid.' else if
> @.ReturnCode = 9 print 'ERROR 9 - The specified Trace Handle is not
> valid.' else if @.ReturnCode = 13 print 'ERROR 13 - Out of memory.
> Returned when there is not enough memory to perform the specified
> action.' else if @.ReturnCode = 16 print 'ERROR 16 - The function is not
> valid for this trace.' else Print 'ERROR x - Unknown Error' GoTo
> ErrorHandler endendErrorHandler:Return @.ReturnCode
>
> The Code that calls the trace
> DECLARE @.FileName NVARCHAR(128)DECLARE @.RunStopTime DATETIMEDECLARE
> @.MaxSize BIGINTDECLARE @.DurationFilter BIGINTDECLARE
> @.TraceID INTEGERDECLARE @.MinutesToRun INTEGERDECLARE @.RC INTEGERDECLARE
> @.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,@.TraceID 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...
>|||Yes - @.DurationFilter needs to be BIGINT, and is - I ran with INT before, and
got error message saying that it needed to be BIGINT.
"Server: Msg 214, Level 16, State 3, Procedure sp_trace_setfilter, Line 272
Procedure expects parameter '@.value' of type 'bigint'."
"Olu Adedeji" wrote:
> 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. @.DurationFilterValue BIGINT = 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 <> 0 Begin if @.ReturnCode = 1 Print 'Error 1 - Unknown
> > error.' if @.ReturnCode = 10 Print 'Error 10 - Invalid options. Returned when
> > options specified are incompatible.' if @.ReturnCode = 12 Print 'Error 12 -
> > Cannot create tracefile - check if file already exists, or this trace already
> > running' if @.ReturnCode = 13 Print 'Error 13 - Out of memory. Returned when
> > there is not enough memory to perform the specified action.' if @.ReturnCode => > 14 Print 'Error 14 - Invalid stop time. Returned when the stop time
> > specified has already happened.' if @.ReturnCode = 15 Print 'Error 15 -
> > Invalid parameters. Returned when the user supplied incompatible
> > parameters.' else Print '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 <> 0 Begin if @.ReturnCode = 13 Print
> > 'ERROR 13 - Out of memory. Returned when there is not enough memory to
> > perform the specified action.' else if @.ReturnCode = 9 Print 'ERROR 9 -
> > The specified Trace Handle is not valid.' else if @.ReturnCode = 8 print
> > 'ERROR 8 - The specified Status is not valid.' else Print 'ERROR 1 - Unknown
> > Error' GoTo ErrorHandlerendexec sp_trace_setfilter
> > @.TraceId,@.ColumnId,@.LogicalOperator,@.ComparisonOperator,@.Valueselect
> > @.ReturnCode=@.@.Errorif @.ReturnCode <> 0 Begin if @.ReturnCode = 1 print 'ERROR
> > 1 - Unknown error.' else if @.ReturnCode = 2 Print 'ERROR 2 - The trace is
> > currently running. Changing the trace at this time will result in an
> > error.' else if @.ReturnCode = 4 Print 'ERROR 4 - The specified Column is not
> > valid.' else if @.ReturnCode = 5 print 'ERROR 5 - The specified Column is not
> > allowed for filtering.' else if @.ReturnCode = 6 print 'ERROR 6 - The
> > specified Comparison Operator is not valid. ' else if @.ReturnCode = 7 print
> > 'ERROR 7 - The specified Logical Operator is not valid.' else if @.ReturnCode
> > = 9 print 'ERROR 9 - The specified Trace Handle is not valid.' else if
> > @.ReturnCode = 13 print 'ERROR 13 - Out of memory. Returned when there is not
> > enough memory to perform the specified action.' else if @.ReturnCode => > 16 print 'ERROR 16 - The function is not valid for this trace.' else Print
> > 'ERROR x - Unknown Error' GoTo ErrorHandlerendif @.DurationFilterValue <>
> > 0 begin set @.columnId = 13 --Duration set @.ComparisonOperator = 2
> > --Greater than exec sp_trace_setfilter
> > @.TraceId,@.ColumnId,@.LogicalOperator,@.ComparisonOperator,@.DurationFilterValue select
> > @.ReturnCode=@.@.Error if @.ReturnCode <> 0 Begin if @.ReturnCode = 1 print
> > 'ERROR 1 - Unknown error.' else if @.ReturnCode = 2 Print 'ERROR 2 - The
> > trace is currently running. Changing the trace at this time will result in an
> > error.' else if @.ReturnCode = 4 Print 'ERROR 4 - The specified Column is
> > not valid.' else if @.ReturnCode = 5 print 'ERROR 5 - The specified Column
> > is not allowed for filtering.' else if @.ReturnCode = 6 print 'ERROR 6 -
> > The specified Comparison Operator is not valid. ' else if @.ReturnCode => > 7 print 'ERROR 7 - The specified Logical Operator is not valid.' else if
> > @.ReturnCode = 9 print 'ERROR 9 - The specified Trace Handle is not
> > valid.' else if @.ReturnCode = 13 print 'ERROR 13 - Out of memory.
> > Returned when there is not enough memory to perform the specified
> > action.' else if @.ReturnCode = 16 print 'ERROR 16 - The function is not
> > valid for this trace.' else Print 'ERROR x - Unknown Error' GoTo
> > ErrorHandler endendErrorHandler:Return @.ReturnCode
> >
> >
> > The Code that calls the trace
> >
> > DECLARE @.FileName NVARCHAR(128)DECLARE @.RunStopTime DATETIMEDECLARE
> > @.MaxSize BIGINTDECLARE @.DurationFilter BIGINTDECLARE
> > @.TraceID INTEGERDECLARE @.MinutesToRun INTEGERDECLARE @.RC INTEGERDECLARE
> > @.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,@.TraceID 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...
> >|||I have never had problems with this procedure, however I try and stick with
the syntax format as seen below(one of my earlier posts ie. using
@.bigintfilter and @.intfilter input parameters) because of the nature of the
procedure(ie. it cannot be viewed using sp_helptext for obvious reasons)
CREATE PROCEDURE sp_DBA_LRQ
@.duration int, -- in minutes
@.QueryDuration int = 5, -- in seconds
@.filename nvarchar(100)= @.@.servername, -- script appends yyyymmddhh
@.dbname sysname = null, -- the database to monitor (null = monitor all
databases)
@.maxfilesize bigint = 20, -- expected size of trc file in MB
@.filedir varchar(1000) = 'C:\Temp\' -- file directory - remeber to include
backslash
AS
SET NOCOUNT ON
-- Name: sp_DBA_LRQ
-- Date: 22/10/2004
-- Author: Olu Adedeji
-- Note: auto set @.duration (mins)
-- auto set @.filename (no extension)
-- monitor @.dbname (database you wish to run the sql against)
-- The latest file will always be servername_yyyymmdd.trc
-- to use this locally set @.filedir = c:\temp\ -- remember to add the '\'
at the end of the directory
-- Capture Long Running Queries(LRQ)
--++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
++++++++++++++++++++++++++++++++
-- WHAT WHO WHEN
--++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
+++++++++++++++++++++++++++++++++
-- Version: 1.00 - Base version was originally written(sp_DBA_GatherSQL) to
-- Resolve Execution plan annomalies on prod servers Olu
Adedeji 16/12/2004
-- Version: 1.04 - customised to capture specific information for PrecisDM
Olu Adedeji 16/12/2004
-- Version: 1.05 - customised to capture long running Queries Olu
Adedeji 22/12/2004
-- I have included to capture SQLBatches and Stored Procs
-- Display Procedure information
Print 'Application: SQL Profiler'
Print 'Procedure: sp_DBA_LRQ'
Print 'Function: Capture Long Running Queries(LRQ) on ' + @.@.servername + '
for ' + convert(varchar,@.duration) + '(mins)'
Print 'MaxFileSize: '+ convert(varchar,@.maxfilesize) + 'Mb'
-- clear down temp table
if (select object_id('tempdb.dbo.##fileexists') from
tempdb.dbo.sysobjects(nolock) where name = '##fileexists') is not null
drop table ##fileexists
-- Create a Queue
declare @.rc int,
@.TraceID int, -- sqlassigns the trace ID
@.DateTime datetime, -- trace stop time
@.CurrentTime datetime, -- current time
@.appendDate int, -- append datestamp
@.Cmd varchar(1000),
@.RenameCmd varchar(1000),
@.newFileName varchar(1000)
-- auto set stoptime
set @.CurrentTime = current_timestamp
set @.DateTime = dateadd(mi,@.duration,@.CurrentTime)
--set @.maxfilesize = 50 -- set max file size to 10Mb
set @.filename = @.filename + '_' + (select
convert(varchar,current_timestamp,112))
set @.newFileName = @.filename
set @.filename = @.filedir + @.filename
-- create temp table
create table ##fileexists (FileExists int, DirectoryExists int,
ParentDirectoryExists int)
-- New file name to rename to
declare @.ActualFilename varchar(100) -- actual file to rename (with .trc
extension)
set @.ActualFilename = @.filename + '.trc'
-- populate temp table
insert into ##fileexists
exec master.dbo.xp_fileexist @.Actualfilename
-- Check that file exists and rename file or proceed if it does not exist
if (select fileexists from ##fileexists where fileexists = 1) is not null
begin
-- I have used 114 to allow for 24h time conversion (similar to how
logshipping works)
select @.appendDate = (select CASE WHEN
convert(varchar,datepart(hh,current_timestamp),114) < 10 THEN '0' +
convert(varchar,datepart(hh,current_timestamp),114)
WHEN convert(varchar,datepart(hh,current_timestamp),114) >= 10 THEN
convert(varchar,datepart(hh,current_timestamp),114)
END
+ CASE WHEN convert(varchar,datepart(mi,current_timestamp),114)< 10
THEN '0' + convert(varchar,datepart(mi,current_timestamp),114)
WHEN convert(varchar,datepart(mi,current_timestamp),114)>= 10 THEN
convert(varchar,datepart(mi,current_timestamp),114)
END)
select @.RenameCmd = 'rename ' + @.filename + '.trc ' + @.NewFileName + '_'
+ convert(varchar,@.AppendDate) + '.trc'
-- display the rename command (useful if file exists)
Print @.RenameCmd
exec master.dbo.xp_cmdshell @.RenameCmd,NO_OUTPUT
End
exec @.rc = sp_trace_create @.TraceID output, 0, @.filename, @.maxfilesize,
@.Datetime
if (@.rc != 0) goto error
-- Client side File and Table cannot be scripted
-- Set the events
declare @.on bit
set @.on = 1
--++++++++++++++++++++++++++++++++++++++
-- Capture RPC:Completed
--++++++++++++++++++++++++++++++++++++++
exec sp_trace_setevent @.TraceID, 10, 1, @.on
exec sp_trace_setevent @.TraceID, 10, 2, @.on
exec sp_trace_setevent @.TraceID, 10, 3, @.on
exec sp_trace_setevent @.TraceID, 10, 8, @.on
exec sp_trace_setevent @.TraceID, 10, 9, @.on
exec sp_trace_setevent @.TraceID, 10, 10, @.on
exec sp_trace_setevent @.TraceID, 10, 11, @.on
exec sp_trace_setevent @.TraceID, 10, 12, @.on
exec sp_trace_setevent @.TraceID, 10, 13, @.on
exec sp_trace_setevent @.TraceID, 10, 14, @.on
exec sp_trace_setevent @.TraceID, 10, 15, @.on
exec sp_trace_setevent @.TraceID, 10, 21, @.on
exec sp_trace_setevent @.TraceID, 10, 26, @.on
exec sp_trace_setevent @.TraceID, 10, 35, @.on
--++++++++++++++++++++++++++++++++++++++
-- Capture SP:Completed
--++++++++++++++++++++++++++++++++++++++
exec sp_trace_setevent @.TraceID, 43, 1, @.on
exec sp_trace_setevent @.TraceID, 43, 2, @.on
exec sp_trace_setevent @.TraceID, 43, 3, @.on
exec sp_trace_setevent @.TraceID, 43, 8, @.on
exec sp_trace_setevent @.TraceID, 43, 9, @.on
exec sp_trace_setevent @.TraceID, 43, 10, @.on
exec sp_trace_setevent @.TraceID, 43, 11, @.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, 15, @.on
exec sp_trace_setevent @.TraceID, 43, 21, @.on
exec sp_trace_setevent @.TraceID, 43, 26, @.on
exec sp_trace_setevent @.TraceID, 43, 35, @.on
--++++++++++++++++++++++++++++++++++++++
--Capture SQL:BatchCompleted
--++++++++++++++++++++++++++++++++++++++
exec sp_trace_setevent @.TraceID, 12, 1, @.on
exec sp_trace_setevent @.TraceID, 12, 2, @.on
exec sp_trace_setevent @.TraceID, 12, 3, @.on
exec sp_trace_setevent @.TraceID, 12, 8, @.on
exec sp_trace_setevent @.TraceID, 12, 9, @.on
exec sp_trace_setevent @.TraceID, 12, 10, @.on
exec sp_trace_setevent @.TraceID, 12, 11, @.on
exec sp_trace_setevent @.TraceID, 12, 12, @.on
exec sp_trace_setevent @.TraceID, 12, 13, @.on
exec sp_trace_setevent @.TraceID, 12, 14, @.on
exec sp_trace_setevent @.TraceID, 12, 15, @.on
exec sp_trace_setevent @.TraceID, 12, 21, @.on
exec sp_trace_setevent @.TraceID, 12, 26, @.on
exec sp_trace_setevent @.TraceID, 12, 35, @.on
--++++++++++++++++++++++++++++++++++++++
-- Capture Generic Information
--++++++++++++++++++++++++++++++++++++++
exec sp_trace_setevent @.TraceID, 70, 1, @.on
exec sp_trace_setevent @.TraceID, 70, 2, @.on
exec sp_trace_setevent @.TraceID, 70, 3, @.on
exec sp_trace_setevent @.TraceID, 70, 8, @.on
exec sp_trace_setevent @.TraceID, 70, 9, @.on
exec sp_trace_setevent @.TraceID, 70, 10, @.on
exec sp_trace_setevent @.TraceID, 70, 11, @.on
exec sp_trace_setevent @.TraceID, 70, 12, @.on
exec sp_trace_setevent @.TraceID, 70, 14, @.on
exec sp_trace_setevent @.TraceID, 70, 21, @.on
exec sp_trace_setevent @.TraceID, 70, 26, @.on
exec sp_trace_setevent @.TraceID, 70, 35, @.on
exec sp_trace_setevent @.TraceID, 71, 1, @.on
exec sp_trace_setevent @.TraceID, 71, 2, @.on
exec sp_trace_setevent @.TraceID, 71, 3, @.on
exec sp_trace_setevent @.TraceID, 71, 8, @.on
exec sp_trace_setevent @.TraceID, 71, 9, @.on
exec sp_trace_setevent @.TraceID, 71, 10, @.on
exec sp_trace_setevent @.TraceID, 71, 11, @.on
exec sp_trace_setevent @.TraceID, 71, 12, @.on
exec sp_trace_setevent @.TraceID, 71, 14, @.on
exec sp_trace_setevent @.TraceID, 71, 21, @.on
exec sp_trace_setevent @.TraceID, 71, 26, @.on
exec sp_trace_setevent @.TraceID, 71, 35, @.on
exec sp_trace_setevent @.TraceID, 72, 1, @.on
exec sp_trace_setevent @.TraceID, 72, 2, @.on
exec sp_trace_setevent @.TraceID, 72, 3, @.on
exec sp_trace_setevent @.TraceID, 72, 8, @.on
exec sp_trace_setevent @.TraceID, 72, 9, @.on
exec sp_trace_setevent @.TraceID, 72, 10, @.on
exec sp_trace_setevent @.TraceID, 72, 11, @.on
exec sp_trace_setevent @.TraceID, 72, 12, @.on
exec sp_trace_setevent @.TraceID, 72, 14, @.on
exec sp_trace_setevent @.TraceID, 72, 21, @.on
exec sp_trace_setevent @.TraceID, 72, 26, @.on
exec sp_trace_setevent @.TraceID, 72, 35, @.on
exec sp_trace_setevent @.TraceID, 74, 1, @.on
exec sp_trace_setevent @.TraceID, 74, 2, @.on
exec sp_trace_setevent @.TraceID, 74, 3, @.on
exec sp_trace_setevent @.TraceID, 74, 8, @.on
exec sp_trace_setevent @.TraceID, 74, 9, @.on
exec sp_trace_setevent @.TraceID, 74, 10, @.on
exec sp_trace_setevent @.TraceID, 74, 11, @.on
exec sp_trace_setevent @.TraceID, 74, 12, @.on
exec sp_trace_setevent @.TraceID, 74, 14, @.on
exec sp_trace_setevent @.TraceID, 74, 21, @.on
exec sp_trace_setevent @.TraceID, 74, 26, @.on
exec sp_trace_setevent @.TraceID, 74, 35, @.on
exec sp_trace_setevent @.TraceID, 77, 1, @.on
exec sp_trace_setevent @.TraceID, 77, 2, @.on
exec sp_trace_setevent @.TraceID, 77, 3, @.on
exec sp_trace_setevent @.TraceID, 77, 8, @.on
exec sp_trace_setevent @.TraceID, 77, 9, @.on
exec sp_trace_setevent @.TraceID, 77, 10, @.on
exec sp_trace_setevent @.TraceID, 77, 11, @.on
exec sp_trace_setevent @.TraceID, 77, 12, @.on
exec sp_trace_setevent @.TraceID, 77, 14, @.on
exec sp_trace_setevent @.TraceID, 77, 21, @.on
exec sp_trace_setevent @.TraceID, 77, 26, @.on
exec sp_trace_setevent @.TraceID, 77, 35, @.on
exec sp_trace_setevent @.TraceID, 78, 1, @.on
exec sp_trace_setevent @.TraceID, 78, 2, @.on
exec sp_trace_setevent @.TraceID, 78, 3, @.on
exec sp_trace_setevent @.TraceID, 78, 8, @.on
exec sp_trace_setevent @.TraceID, 78, 9, @.on
exec sp_trace_setevent @.TraceID, 78, 10, @.on
exec sp_trace_setevent @.TraceID, 78, 11, @.on
exec sp_trace_setevent @.TraceID, 78, 12, @.on
exec sp_trace_setevent @.TraceID, 78, 14, @.on
exec sp_trace_setevent @.TraceID, 78, 21, @.on
exec sp_trace_setevent @.TraceID, 78, 26, @.on
exec sp_trace_setevent @.TraceID, 78, 35, @.on
exec sp_trace_setevent @.TraceID, 100, 1, @.on
exec sp_trace_setevent @.TraceID, 100, 2, @.on
exec sp_trace_setevent @.TraceID, 100, 3, @.on
exec sp_trace_setevent @.TraceID, 100, 8, @.on
exec sp_trace_setevent @.TraceID, 100, 9, @.on
exec sp_trace_setevent @.TraceID, 100, 10, @.on
exec sp_trace_setevent @.TraceID, 100, 11, @.on
exec sp_trace_setevent @.TraceID, 100, 12, @.on
exec sp_trace_setevent @.TraceID, 100, 14, @.on
exec sp_trace_setevent @.TraceID, 100, 21, @.on
exec sp_trace_setevent @.TraceID, 100, 26, @.on
exec sp_trace_setevent @.TraceID, 100, 35, @.on
-- Set the Filters
declare @.intfilter int
declare @.bigintfilter bigint
-- set database filter if @.dbname is not null
if @.dbname is not null
begin
-- check @.dbname is on the server
if (select db_id(@.dbname)) is not null
set @.intfilter = db_id(@.dbname)
exec sp_trace_setfilter @.TraceID, 3, 1, 0, @.intfilter
end
exec sp_trace_setfilter @.TraceID, 10, 0, 7, N'SQL Profiler'
-- set filter for Long Running Query(LRQ) Duration
-- Added this 22/12/2004
select @.bigintfilter = (@.QueryDuration*1000) -- need to set this in
milliseconds(ms)
exec sp_trace_setfilter @.TraceID, 13, 0, 4, @.bigintfilter
-- 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:
-- display Trace information (can output this to a logfile in sqlagent)
select * from ::fn_trace_getinfo(default)
-- clear down temp table
if (select object_id('tempdb.dbo.##fileexists') from
tempdb.dbo.sysobjects(nolock) where name = '##fileexists') is not null
drop table ##fileexists
GO
"Wanderer" <Wanderer@.discussions.microsoft.com> wrote in message
news:E8FD0981-2E9F-4D42-B489-304E8AE57E16@.microsoft.com...
> Yes - @.DurationFilter needs to be BIGINT, and is - I ran with INT before,
and
> got error message saying that it needed to be BIGINT.
> "Server: Msg 214, Level 16, State 3, Procedure sp_trace_setfilter, Line
272
> Procedure expects parameter '@.value' of type 'bigint'."
> "Olu Adedeji" wrote:
> > 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. @.DurationFilterValue BIGINT = 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 <> 0 Begin if @.ReturnCode = 1 Print 'Error 1 - Unknown
> > > error.' if @.ReturnCode = 10 Print 'Error 10 - Invalid options.
Returned when
> > > options specified are incompatible.' if @.ReturnCode = 12 Print 'Error
12 -
> > > Cannot create tracefile - check if file already exists, or this trace
already
> > > running' if @.ReturnCode = 13 Print 'Error 13 - Out of memory. Returned
when
> > > there is not enough memory to perform the specified action.' if
@.ReturnCode => > > 14 Print 'Error 14 - Invalid stop time. Returned when the stop time
> > > specified has already happened.' if @.ReturnCode = 15 Print 'Error 15 -
> > > Invalid parameters. Returned when the user supplied incompatible
> > > parameters.' else Print '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 <> 0 Begin if @.ReturnCode = 13 Print
> > > 'ERROR 13 - Out of memory. Returned when there is not enough memory to
> > > perform the specified action.' else if @.ReturnCode = 9 Print 'ERROR
9 -
> > > The specified Trace Handle is not valid.' else if @.ReturnCode = 8
print
> > > 'ERROR 8 - The specified Status is not valid.' else Print 'ERROR 1 -
Unknown
> > > Error' GoTo ErrorHandlerendexec sp_trace_setfilter
> > > @.TraceId,@.ColumnId,@.LogicalOperator,@.ComparisonOperator,@.Valueselect
> > > @.ReturnCode=@.@.Errorif @.ReturnCode <> 0 Begin if @.ReturnCode = 1 print
'ERROR
> > > 1 - Unknown error.' else if @.ReturnCode = 2 Print 'ERROR 2 - The trace
is
> > > currently running. Changing the trace at this time will result in an
> > > error.' else if @.ReturnCode = 4 Print 'ERROR 4 - The specified Column
is not
> > > valid.' else if @.ReturnCode = 5 print 'ERROR 5 - The specified Column
is not
> > > allowed for filtering.' else if @.ReturnCode = 6 print 'ERROR 6 - The
> > > specified Comparison Operator is not valid. ' else if @.ReturnCode = 7
print
> > > 'ERROR 7 - The specified Logical Operator is not valid.' else if
@.ReturnCode
> > > = 9 print 'ERROR 9 - The specified Trace Handle is not valid.' else if
> > > @.ReturnCode = 13 print 'ERROR 13 - Out of memory. Returned when there
is not
> > > enough memory to perform the specified action.' else if @.ReturnCode => > > 16 print 'ERROR 16 - The function is not valid for this trace.' else
Print
> > > 'ERROR x - Unknown Error' GoTo ErrorHandlerendif @.DurationFilterValue
<>
> > > 0 begin set @.columnId = 13 --Duration set @.ComparisonOperator = 2
> > > --Greater than exec sp_trace_setfilter
> > >
@.TraceId,@.ColumnId,@.LogicalOperator,@.ComparisonOperator,@.DurationFilterValue
select
> > > @.ReturnCode=@.@.Error if @.ReturnCode <> 0 Begin if @.ReturnCode = 1 print
> > > 'ERROR 1 - Unknown error.' else if @.ReturnCode = 2 Print 'ERROR 2 -
The
> > > trace is currently running. Changing the trace at this time will
result in an
> > > error.' else if @.ReturnCode = 4 Print 'ERROR 4 - The specified Column
is
> > > not valid.' else if @.ReturnCode = 5 print 'ERROR 5 - The specified
Column
> > > is not allowed for filtering.' else if @.ReturnCode = 6 print 'ERROR
6 -
> > > The specified Comparison Operator is not valid. ' else if @.ReturnCode
=> > > 7 print 'ERROR 7 - The specified Logical Operator is not valid.' else
if
> > > @.ReturnCode = 9 print 'ERROR 9 - The specified Trace Handle is not
> > > valid.' else if @.ReturnCode = 13 print 'ERROR 13 - Out of memory.
> > > Returned when there is not enough memory to perform the specified
> > > action.' else if @.ReturnCode = 16 print 'ERROR 16 - The function is
not
> > > valid for this trace.' else Print 'ERROR x - Unknown Error' GoTo
> > > ErrorHandler endendErrorHandler:Return @.ReturnCode
> > >
> > >
> > > The Code that calls the trace
> > >
> > > DECLARE @.FileName NVARCHAR(128)DECLARE @.RunStopTime DATETIMEDECLARE
> > > @.MaxSize BIGINTDECLARE @.DurationFilter BIGINTDECLARE
> > > @.TraceID INTEGERDECLARE @.MinutesToRun INTEGERDECLARE @.RC
INTEGERDECLARE
> > > @.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,@.TraceID 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...
> > >|||I'm not having datatype errors. The SP runs fine, EXCEPT that it doesn't
seem to apply the filter. When I open the trace file in Profiler, there are
NO filters set. Yet the SP_TRACE_SETFILTER does not return any error's
(you'll see I do do error checking after the SP_TRACE_SETFILTER commands.
"Olu Adedeji" wrote:
> I have never had problems with this procedure, however I try and stick with
> the syntax format as seen below(one of my earlier posts ie. using
> @.bigintfilter and @.intfilter input parameters) because of the nature of the
> procedure(ie. it cannot be viewed using sp_helptext for obvious reasons)
>
> CREATE PROCEDURE sp_DBA_LRQ
> @.duration int, -- in minutes
> @.QueryDuration int = 5, -- in seconds
> @.filename nvarchar(100)= @.@.servername, -- script appends yyyymmddhh
> @.dbname sysname = null, -- the database to monitor (null = monitor all
> databases)
> @.maxfilesize bigint = 20, -- expected size of trc file in MB
> @.filedir varchar(1000) = 'C:\Temp\' -- file directory - remeber to include
> backslash
> AS
> SET NOCOUNT ON
> -- Name: sp_DBA_LRQ
> -- Date: 22/10/2004
> -- Author: Olu Adedeji
> -- Note: auto set @.duration (mins)
> -- auto set @.filename (no extension)
> -- monitor @.dbname (database you wish to run the sql against)
> -- The latest file will always be servername_yyyymmdd.trc
> -- to use this locally set @.filedir = c:\temp\ -- remember to add the '\'
> at the end of the directory
> -- Capture Long Running Queries(LRQ)
> --++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
> ++++++++++++++++++++++++++++++++
> -- WHAT WHO WHEN
> --++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
> +++++++++++++++++++++++++++++++++
> -- Version: 1.00 - Base version was originally written(sp_DBA_GatherSQL) to
> -- Resolve Execution plan annomalies on prod servers Olu
> Adedeji 16/12/2004
> -- Version: 1.04 - customised to capture specific information for PrecisDM
> Olu Adedeji 16/12/2004
> -- Version: 1.05 - customised to capture long running Queries Olu
> Adedeji 22/12/2004
> -- I have included to capture SQLBatches and Stored Procs
> -- Display Procedure information
> Print 'Application: SQL Profiler'
> Print 'Procedure: sp_DBA_LRQ'
> Print 'Function: Capture Long Running Queries(LRQ) on ' + @.@.servername + '
> for ' + convert(varchar,@.duration) + '(mins)'
> Print 'MaxFileSize: '+ convert(varchar,@.maxfilesize) + 'Mb'
> -- clear down temp table
> if (select object_id('tempdb.dbo.##fileexists') from
> tempdb.dbo.sysobjects(nolock) where name = '##fileexists') is not null
> drop table ##fileexists
> -- Create a Queue
> declare @.rc int,
> @.TraceID int, -- sqlassigns the trace ID
> @.DateTime datetime, -- trace stop time
> @.CurrentTime datetime, -- current time
> @.appendDate int, -- append datestamp
> @.Cmd varchar(1000),
> @.RenameCmd varchar(1000),
> @.newFileName varchar(1000)
> -- auto set stoptime
> set @.CurrentTime = current_timestamp
> set @.DateTime = dateadd(mi,@.duration,@.CurrentTime)
> --set @.maxfilesize = 50 -- set max file size to 10Mb
> set @.filename = @.filename + '_' + (select
> convert(varchar,current_timestamp,112))
> set @.newFileName = @.filename
> set @.filename = @.filedir + @.filename
> -- create temp table
> create table ##fileexists (FileExists int, DirectoryExists int,
> ParentDirectoryExists int)
> -- New file name to rename to
> declare @.ActualFilename varchar(100) -- actual file to rename (with .trc
> extension)
> set @.ActualFilename = @.filename + '.trc'
> -- populate temp table
> insert into ##fileexists
> exec master.dbo.xp_fileexist @.Actualfilename
>
> -- Check that file exists and rename file or proceed if it does not exist
> if (select fileexists from ##fileexists where fileexists = 1) is not null
> begin
> -- I have used 114 to allow for 24h time conversion (similar to how
> logshipping works)
> select @.appendDate = (select CASE WHEN
> convert(varchar,datepart(hh,current_timestamp),114) < 10 THEN '0' +
> convert(varchar,datepart(hh,current_timestamp),114)
> WHEN convert(varchar,datepart(hh,current_timestamp),114) >= 10 THEN
> convert(varchar,datepart(hh,current_timestamp),114)
> END
> + CASE WHEN convert(varchar,datepart(mi,current_timestamp),114)< 10
> THEN '0' + convert(varchar,datepart(mi,current_timestamp),114)
> WHEN convert(varchar,datepart(mi,current_timestamp),114)>= 10 THEN
> convert(varchar,datepart(mi,current_timestamp),114)
> END)
> select @.RenameCmd = 'rename ' + @.filename + '.trc ' + @.NewFileName + '_'
> + convert(varchar,@.AppendDate) + '.trc'
> -- display the rename command (useful if file exists)
> Print @.RenameCmd
> exec master.dbo.xp_cmdshell @.RenameCmd,NO_OUTPUT
> End
> exec @.rc = sp_trace_create @.TraceID output, 0, @.filename, @.maxfilesize,
> @.Datetime
> if (@.rc != 0) goto error
> -- Client side File and Table cannot be scripted
> -- Set the events
> declare @.on bit
> set @.on = 1
> --++++++++++++++++++++++++++++++++++++++
> -- Capture RPC:Completed
> --++++++++++++++++++++++++++++++++++++++
> exec sp_trace_setevent @.TraceID, 10, 1, @.on
> exec sp_trace_setevent @.TraceID, 10, 2, @.on
> exec sp_trace_setevent @.TraceID, 10, 3, @.on
> exec sp_trace_setevent @.TraceID, 10, 8, @.on
> exec sp_trace_setevent @.TraceID, 10, 9, @.on
> exec sp_trace_setevent @.TraceID, 10, 10, @.on
> exec sp_trace_setevent @.TraceID, 10, 11, @.on
> exec sp_trace_setevent @.TraceID, 10, 12, @.on
> exec sp_trace_setevent @.TraceID, 10, 13, @.on
> exec sp_trace_setevent @.TraceID, 10, 14, @.on
> exec sp_trace_setevent @.TraceID, 10, 15, @.on
> exec sp_trace_setevent @.TraceID, 10, 21, @.on
> exec sp_trace_setevent @.TraceID, 10, 26, @.on
> exec sp_trace_setevent @.TraceID, 10, 35, @.on
> --++++++++++++++++++++++++++++++++++++++
> -- Capture SP:Completed
> --++++++++++++++++++++++++++++++++++++++
> exec sp_trace_setevent @.TraceID, 43, 1, @.on
> exec sp_trace_setevent @.TraceID, 43, 2, @.on
> exec sp_trace_setevent @.TraceID, 43, 3, @.on
> exec sp_trace_setevent @.TraceID, 43, 8, @.on
> exec sp_trace_setevent @.TraceID, 43, 9, @.on
> exec sp_trace_setevent @.TraceID, 43, 10, @.on
> exec sp_trace_setevent @.TraceID, 43, 11, @.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, 15, @.on
> exec sp_trace_setevent @.TraceID, 43, 21, @.on
> exec sp_trace_setevent @.TraceID, 43, 26, @.on
> exec sp_trace_setevent @.TraceID, 43, 35, @.on
> --++++++++++++++++++++++++++++++++++++++
> --Capture SQL:BatchCompleted
> --++++++++++++++++++++++++++++++++++++++
> exec sp_trace_setevent @.TraceID, 12, 1, @.on
> exec sp_trace_setevent @.TraceID, 12, 2, @.on
> exec sp_trace_setevent @.TraceID, 12, 3, @.on
> exec sp_trace_setevent @.TraceID, 12, 8, @.on
> exec sp_trace_setevent @.TraceID, 12, 9, @.on
> exec sp_trace_setevent @.TraceID, 12, 10, @.on
> exec sp_trace_setevent @.TraceID, 12, 11, @.on
> exec sp_trace_setevent @.TraceID, 12, 12, @.on
> exec sp_trace_setevent @.TraceID, 12, 13, @.on
> exec sp_trace_setevent @.TraceID, 12, 14, @.on
> exec sp_trace_setevent @.TraceID, 12, 15, @.on
> exec sp_trace_setevent @.TraceID, 12, 21, @.on
> exec sp_trace_setevent @.TraceID, 12, 26, @.on
> exec sp_trace_setevent @.TraceID, 12, 35, @.on
> --++++++++++++++++++++++++++++++++++++++
> -- Capture Generic Information
> --++++++++++++++++++++++++++++++++++++++
> exec sp_trace_setevent @.TraceID, 70, 1, @.on
> exec sp_trace_setevent @.TraceID, 70, 2, @.on
> exec sp_trace_setevent @.TraceID, 70, 3, @.on
> exec sp_trace_setevent @.TraceID, 70, 8, @.on
> exec sp_trace_setevent @.TraceID, 70, 9, @.on
> exec sp_trace_setevent @.TraceID, 70, 10, @.on
> exec sp_trace_setevent @.TraceID, 70, 11, @.on
> exec sp_trace_setevent @.TraceID, 70, 12, @.on
> exec sp_trace_setevent @.TraceID, 70, 14, @.on
> exec sp_trace_setevent @.TraceID, 70, 21, @.on
> exec sp_trace_setevent @.TraceID, 70, 26, @.on
> exec sp_trace_setevent @.TraceID, 70, 35, @.on
> exec sp_trace_setevent @.TraceID, 71, 1, @.on
> exec sp_trace_setevent @.TraceID, 71, 2, @.on
> exec sp_trace_setevent @.TraceID, 71, 3, @.on
> exec sp_trace_setevent @.TraceID, 71, 8, @.on
> exec sp_trace_setevent @.TraceID, 71, 9, @.on
> exec sp_trace_setevent @.TraceID, 71, 10, @.on
> exec sp_trace_setevent @.TraceID, 71, 11, @.on
> exec sp_trace_setevent @.TraceID, 71, 12, @.on
> exec sp_trace_setevent @.TraceID, 71, 14, @.on
> exec sp_trace_setevent @.TraceID, 71, 21, @.on
> exec sp_trace_setevent @.TraceID, 71, 26, @.on
> exec sp_trace_setevent @.TraceID, 71, 35, @.on
> exec sp_trace_setevent @.TraceID, 72, 1, @.on
> exec sp_trace_setevent @.TraceID, 72, 2, @.on
> exec sp_trace_setevent @.TraceID, 72, 3, @.on
> exec sp_trace_setevent @.TraceID, 72, 8, @.on
> exec sp_trace_setevent @.TraceID, 72, 9, @.on
> exec sp_trace_setevent @.TraceID, 72, 10, @.on
> exec sp_trace_setevent @.TraceID, 72, 11, @.on
> exec sp_trace_setevent @.TraceID, 72, 12, @.on
> exec sp_trace_setevent @.TraceID, 72, 14, @.on
> exec sp_trace_setevent @.TraceID, 72, 21, @.on
> exec sp_trace_setevent @.TraceID, 72, 26, @.on
> exec sp_trace_setevent @.TraceID, 72, 35, @.on
> exec sp_trace_setevent @.TraceID, 74, 1, @.on
> exec sp_trace_setevent @.TraceID, 74, 2, @.on
> exec sp_trace_setevent @.TraceID, 74, 3, @.on
> exec sp_trace_setevent @.TraceID, 74, 8, @.on
> exec sp_trace_setevent @.TraceID, 74, 9, @.on
> exec sp_trace_setevent @.TraceID, 74, 10, @.on
> exec sp_trace_setevent @.TraceID, 74, 11, @.on
> exec sp_trace_setevent @.TraceID, 74, 12, @.on
> exec sp_trace_setevent @.TraceID, 74, 14, @.on
> exec sp_trace_setevent @.TraceID, 74, 21, @.on
> exec sp_trace_setevent @.TraceID, 74, 26, @.on
> exec sp_trace_setevent @.TraceID, 74, 35, @.on
> exec sp_trace_setevent @.TraceID, 77, 1, @.on
> exec sp_trace_setevent @.TraceID, 77, 2, @.on
> exec sp_trace_setevent @.TraceID, 77, 3, @.on
> exec sp_trace_setevent @.TraceID, 77, 8, @.on
> exec sp_trace_setevent @.TraceID, 77, 9, @.on
> exec sp_trace_setevent @.TraceID, 77, 10, @.on
> exec sp_trace_setevent @.TraceID, 77, 11, @.on
> exec sp_trace_setevent @.TraceID, 77, 12, @.on
> exec sp_trace_setevent @.TraceID, 77, 14, @.on
> exec sp_trace_setevent @.TraceID, 77, 21, @.on
> exec sp_trace_setevent @.TraceID, 77, 26, @.on
> exec sp_trace_setevent @.TraceID, 77, 35, @.on
> exec sp_trace_setevent @.TraceID, 78, 1, @.on
> exec sp_trace_setevent @.TraceID, 78, 2, @.on
> exec sp_trace_setevent @.TraceID, 78, 3, @.on
> exec sp_trace_setevent @.TraceID, 78, 8, @.on
> exec sp_trace_setevent @.TraceID, 78, 9, @.on
> exec sp_trace_setevent @.TraceID, 78, 10, @.on
> exec sp_trace_setevent @.TraceID, 78, 11, @.on
> exec sp_trace_setevent @.TraceID, 78, 12, @.on
> exec sp_trace_setevent @.TraceID, 78, 14, @.on
> exec sp_trace_setevent @.TraceID, 78, 21, @.on
> exec sp_trace_setevent @.TraceID, 78, 26, @.on
> exec sp_trace_setevent @.TraceID, 78, 35, @.on
> exec sp_trace_setevent @.TraceID, 100, 1, @.on
> exec sp_trace_setevent @.TraceID, 100, 2, @.on
> exec sp_trace_setevent @.TraceID, 100, 3, @.on
> exec sp_trace_setevent @.TraceID, 100, 8, @.on
> exec sp_trace_setevent @.TraceID, 100, 9, @.on
> exec sp_trace_setevent @.TraceID, 100, 10, @.on
> exec sp_trace_setevent @.TraceID, 100, 11, @.on
> exec sp_trace_setevent @.TraceID, 100, 12, @.on
> exec sp_trace_setevent @.TraceID, 100, 14, @.on
> exec sp_trace_setevent @.TraceID, 100, 21, @.on
> exec sp_trace_setevent @.TraceID, 100, 26, @.on
> exec sp_trace_setevent @.TraceID, 100, 35, @.on
> -- Set the Filters
> declare @.intfilter int
> declare @.bigintfilter bigint
> -- set database filter if @.dbname is not null
> if @.dbname is not null
> begin
> -- check @.dbname is on the server
> if (select db_id(@.dbname)) is not null
> set @.intfilter = db_id(@.dbname)
> exec sp_trace_setfilter @.TraceID, 3, 1, 0, @.intfilter
> end
> exec sp_trace_setfilter @.TraceID, 10, 0, 7, N'SQL Profiler'
> -- set filter for Long Running Query(LRQ) Duration
> -- Added this 22/12/2004
> select @.bigintfilter = (@.QueryDuration*1000) -- need to set this in
> milliseconds(ms)
> exec sp_trace_setfilter @.TraceID, 13, 0, 4, @.bigintfilter
>
> -- 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:
> -- display Trace information (can output this to a logfile in sqlagent)
> select * from ::fn_trace_getinfo(default)
> -- clear down temp table
> if (select object_id('tempdb.dbo.##fileexists') from
> tempdb.dbo.sysobjects(nolock) where name = '##fileexists') is not null
> drop table ##fileexists
> GO
> "Wanderer" <Wanderer@.discussions.microsoft.com> wrote in message
> news:E8FD0981-2E9F-4D42-B489-304E8AE57E16@.microsoft.com...
> > Yes - @.DurationFilter needs to be BIGINT, and is - I ran with INT before,
> and
> > got error message saying that it needed to be BIGINT.
> >
> > "Server: Msg 214, Level 16, State 3, Procedure sp_trace_setfilter, Line
> 272
> > Procedure expects parameter '@.value' of type 'bigint'."
> >
> > "Olu Adedeji" wrote:
> >
> > > 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. @.DurationFilterValue BIGINT = 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 <> 0 Begin if @.ReturnCode = 1 Print 'Error 1 - Unknown
> > > > error.' if @.ReturnCode = 10 Print 'Error 10 - Invalid options.
> Returned when
> > > > options specified are incompatible.' if @.ReturnCode = 12 Print 'Error
> 12 -
> > > > Cannot create tracefile - check if file already exists, or this trace
> already
> > > > running' if @.ReturnCode = 13 Print 'Error 13 - Out of memory. Returned
> when
> > > > there is not enough memory to perform the specified action.' if
> @.ReturnCode => > > > 14 Print 'Error 14 - Invalid stop time. Returned when the stop time
> > > > specified has already happened.' if @.ReturnCode = 15 Print 'Error 15 -
> > > > Invalid parameters. Returned when the user supplied incompatible
> > > > parameters.' else Print '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 <> 0 Begin if @.ReturnCode = 13 Print
> > > > 'ERROR 13 - Out of memory. Returned when there is not enough memory to
> > > > perform the specified action.' else if @.ReturnCode = 9 Print 'ERROR
> 9 -
> > > > The specified Trace Handle is not valid.' else if @.ReturnCode = 8
> print
> > > > 'ERROR 8 - The specified Status is not valid.' else Print 'ERROR 1 -
> Unknown
> > > > Error' GoTo ErrorHandlerendexec sp_trace_setfilter
> > > > @.TraceId,@.ColumnId,@.LogicalOperator,@.ComparisonOperator,@.Valueselect
> > > > @.ReturnCode=@.@.Errorif @.ReturnCode <> 0 Begin if @.ReturnCode = 1 print
> 'ERROR
> > > > 1 - Unknown error.' else if @.ReturnCode = 2 Print 'ERROR 2 - The trace
> is
> > > > currently running. Changing the trace at this time will result in an
> > > > error.' else if @.ReturnCode = 4 Print 'ERROR 4 - The specified Column
> is not
> > > > valid.' else if @.ReturnCode = 5 print 'ERROR 5 - The specified Column
> is not
> > > > allowed for filtering.' else if @.ReturnCode = 6 print 'ERROR 6 - The
> > > > specified Comparison Operator is not valid. ' else if @.ReturnCode = 7
> print
> > > > 'ERROR 7 - The specified Logical Operator is not valid.' else if
> @.ReturnCode
> > > > = 9 print 'ERROR 9 - The specified Trace Handle is not valid.' else if
> > > > @.ReturnCode = 13 print 'ERROR 13 - Out of memory. Returned when there
> is not
> > > > enough memory to perform the specified action.' else if @.ReturnCode => > > > 16 print 'ERROR 16 - The function is not valid for this trace.' else
> Print
> > > > 'ERROR x - Unknown Error' GoTo ErrorHandlerendif @.DurationFilterValue
> <>
> > > > 0 begin set @.columnId = 13 --Duration set @.ComparisonOperator = 2
> > > > --Greater than exec sp_trace_setfilter
> > > >
> @.TraceId,@.ColumnId,@.LogicalOperator,@.ComparisonOperator,@.DurationFilterValue
> select
> > > > @.ReturnCode=@.@.Error if @.ReturnCode <> 0 Begin if @.ReturnCode = 1 print
> > > > 'ERROR 1 - Unknown error.' else if @.ReturnCode = 2 Print 'ERROR 2 -
> The
> > > > trace is currently running. Changing the trace at this time will
> result in an
> > > > error.' else if @.ReturnCode = 4 Print 'ERROR 4 - The specified Column
> is
> > > > not valid.' else if @.ReturnCode = 5 print 'ERROR 5 - The specified
> Column
> > > > is not allowed for filtering.' else if @.ReturnCode = 6 print 'ERROR
> 6 -
> > > > The specified Comparison Operator is not valid. ' else if @.ReturnCode
> => > > > 7 print 'ERROR 7 - The specified Logical Operator is not valid.' else
> if
> > > > @.ReturnCode = 9 print 'ERROR 9 - The specified Trace Handle is not
> > > > valid.' else if @.ReturnCode = 13 print 'ERROR 13 - Out of memory.
> > > > Returned when there is not enough memory to perform the specified
> > > > action.' else if @.ReturnCode = 16 print 'ERROR 16 - The function is
> not
> > > > valid for this trace.' else Print 'ERROR x - Unknown Error' GoTo
> > > > ErrorHandler endendErrorHandler:Return @.ReturnCode
> > > >
> > > >
> > > > The Code that calls the trace
> > > >
> > > > DECLARE @.FileName NVARCHAR(128)DECLARE @.RunStopTime DATETIMEDECLARE
> > > > @.MaxSize BIGINTDECLARE @.DurationFilter BIGINTDECLARE
> > > > @.TraceID INTEGERDECLARE @.MinutesToRun INTEGERDECLARE @.RC
> INTEGERDECLARE
> > > > @.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,@.TraceID 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...
> > > >
>
>|||Wanderer,
as per my email, I was intimating that you open profiler and set the filter
manually, then script out the trace and cut the section that performs the
sp_trace_setfilter into your procedure substituting your parameters for
hardcoded values. then run your procedure and check the trc files to see if
the traces have been set. . from your post it is obvious that profiler does
not understand the parameters you have passed into sp_trace_setfilter and as
a result it is unable to present it in the trc file. PS. out of interest
what are you trying to filter?
PS. I don't dispute that your trace runs fine . . I am merely trying to
help resolve the problem
"Wanderer" <Wanderer@.discussions.microsoft.com> wrote in message
news:B40C9908-9984-452E-88A6-FF3D1A453ABD@.microsoft.com...
> I'm not having datatype errors. The SP runs fine, EXCEPT that it doesn't
> seem to apply the filter. When I open the trace file in Profiler, there
are
> NO filters set. Yet the SP_TRACE_SETFILTER does not return any error's
> (you'll see I do do error checking after the SP_TRACE_SETFILTER commands.
>
> "Olu Adedeji" wrote:
> > I have never had problems with this procedure, however I try and stick
with
> > the syntax format as seen below(one of my earlier posts ie. using
> > @.bigintfilter and @.intfilter input parameters) because of the nature of
the
> > procedure(ie. it cannot be viewed using sp_helptext for obvious reasons)
> >
> >
> > CREATE PROCEDURE sp_DBA_LRQ
> > @.duration int, -- in minutes
> > @.QueryDuration int = 5, -- in seconds
> > @.filename nvarchar(100)= @.@.servername, -- script appends yyyymmddhh
> > @.dbname sysname = null, -- the database to monitor (null = monitor all
> > databases)
> > @.maxfilesize bigint = 20, -- expected size of trc file in MB
> > @.filedir varchar(1000) = 'C:\Temp\' -- file directory - remeber to
include
> > backslash
> >
> > AS
> >
> > SET NOCOUNT ON
> >
> > -- Name: sp_DBA_LRQ
> > -- Date: 22/10/2004
> > -- Author: Olu Adedeji
> > -- Note: auto set @.duration (mins)
> > -- auto set @.filename (no extension)
> > -- monitor @.dbname (database you wish to run the sql against)
> > -- The latest file will always be servername_yyyymmdd.trc
> > -- to use this locally set @.filedir = c:\temp\ -- remember to add the
'\'
> > at the end of the directory
> > -- Capture Long Running Queries(LRQ)
> >
> --++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
++
> > ++++++++++++++++++++++++++++++++
> > -- WHAT WHO WHEN
> --++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
++
> > +++++++++++++++++++++++++++++++++
> > -- Version: 1.00 - Base version was originally written(sp_DBA_GatherSQL)
to
> > -- Resolve Execution plan annomalies on prod servers Olu
> > Adedeji 16/12/2004
> > -- Version: 1.04 - customised to capture specific information for
PrecisDM
> > Olu Adedeji 16/12/2004
> > -- Version: 1.05 - customised to capture long running Queries Olu
> > Adedeji 22/12/2004
> > -- I have included to capture SQLBatches and Stored Procs
> >
> > -- Display Procedure information
> >
> > Print 'Application: SQL Profiler'
> > Print 'Procedure: sp_DBA_LRQ'
> > Print 'Function: Capture Long Running Queries(LRQ) on ' + @.@.servername +
'
> > for ' + convert(varchar,@.duration) + '(mins)'
> > Print 'MaxFileSize: '+ convert(varchar,@.maxfilesize) + 'Mb'
> >
> > -- clear down temp table
> > if (select object_id('tempdb.dbo.##fileexists') from
> > tempdb.dbo.sysobjects(nolock) where name = '##fileexists') is not null
> > drop table ##fileexists
> >
> > -- Create a Queue
> > declare @.rc int,
> > @.TraceID int, -- sqlassigns the trace ID
> > @.DateTime datetime, -- trace stop time
> > @.CurrentTime datetime, -- current time
> > @.appendDate int, -- append datestamp
> > @.Cmd varchar(1000),
> > @.RenameCmd varchar(1000),
> > @.newFileName varchar(1000)
> >
> > -- auto set stoptime
> > set @.CurrentTime = current_timestamp
> > set @.DateTime = dateadd(mi,@.duration,@.CurrentTime)
> > --set @.maxfilesize = 50 -- set max file size to 10Mb
> > set @.filename = @.filename + '_' + (select
> > convert(varchar,current_timestamp,112))
> > set @.newFileName = @.filename
> > set @.filename = @.filedir + @.filename
> >
> > -- create temp table
> > create table ##fileexists (FileExists int, DirectoryExists int,
> > ParentDirectoryExists int)
> >
> > -- New file name to rename to
> > declare @.ActualFilename varchar(100) -- actual file to rename (with .trc
> > extension)
> > set @.ActualFilename = @.filename + '.trc'
> >
> > -- populate temp table
> > insert into ##fileexists
> > exec master.dbo.xp_fileexist @.Actualfilename
> >
> >
> > -- Check that file exists and rename file or proceed if it does not
exist
> > if (select fileexists from ##fileexists where fileexists = 1) is not
null
> > begin
> > -- I have used 114 to allow for 24h time conversion (similar to how
> > logshipping works)
> > select @.appendDate = (select CASE WHEN
> > convert(varchar,datepart(hh,current_timestamp),114) < 10 THEN '0' +
> > convert(varchar,datepart(hh,current_timestamp),114)
> > WHEN convert(varchar,datepart(hh,current_timestamp),114) >= 10 THEN
> > convert(varchar,datepart(hh,current_timestamp),114)
> > END
> > + CASE WHEN convert(varchar,datepart(mi,current_timestamp),114)< 10
> > THEN '0' + convert(varchar,datepart(mi,current_timestamp),114)
> > WHEN convert(varchar,datepart(mi,current_timestamp),114)>= 10 THEN
> > convert(varchar,datepart(mi,current_timestamp),114)
> > END)
> > select @.RenameCmd = 'rename ' + @.filename + '.trc ' + @.NewFileName +
'_'
> > + convert(varchar,@.AppendDate) + '.trc'
> >
> > -- display the rename command (useful if file exists)
> > Print @.RenameCmd
> > exec master.dbo.xp_cmdshell @.RenameCmd,NO_OUTPUT
> > End
> >
> > exec @.rc = sp_trace_create @.TraceID output, 0, @.filename, @.maxfilesize,
> > @.Datetime
> > if (@.rc != 0) goto error
> >
> > -- Client side File and Table cannot be scripted
> >
> > -- Set the events
> > declare @.on bit
> > set @.on = 1
> >
> > --++++++++++++++++++++++++++++++++++++++
> > -- Capture RPC:Completed
> > --++++++++++++++++++++++++++++++++++++++
> > exec sp_trace_setevent @.TraceID, 10, 1, @.on
> > exec sp_trace_setevent @.TraceID, 10, 2, @.on
> > exec sp_trace_setevent @.TraceID, 10, 3, @.on
> > exec sp_trace_setevent @.TraceID, 10, 8, @.on
> > exec sp_trace_setevent @.TraceID, 10, 9, @.on
> > exec sp_trace_setevent @.TraceID, 10, 10, @.on
> > exec sp_trace_setevent @.TraceID, 10, 11, @.on
> > exec sp_trace_setevent @.TraceID, 10, 12, @.on
> > exec sp_trace_setevent @.TraceID, 10, 13, @.on
> > exec sp_trace_setevent @.TraceID, 10, 14, @.on
> > exec sp_trace_setevent @.TraceID, 10, 15, @.on
> > exec sp_trace_setevent @.TraceID, 10, 21, @.on
> > exec sp_trace_setevent @.TraceID, 10, 26, @.on
> > exec sp_trace_setevent @.TraceID, 10, 35, @.on
> > --++++++++++++++++++++++++++++++++++++++
> > -- Capture SP:Completed
> > --++++++++++++++++++++++++++++++++++++++
> > exec sp_trace_setevent @.TraceID, 43, 1, @.on
> > exec sp_trace_setevent @.TraceID, 43, 2, @.on
> > exec sp_trace_setevent @.TraceID, 43, 3, @.on
> > exec sp_trace_setevent @.TraceID, 43, 8, @.on
> > exec sp_trace_setevent @.TraceID, 43, 9, @.on
> > exec sp_trace_setevent @.TraceID, 43, 10, @.on
> > exec sp_trace_setevent @.TraceID, 43, 11, @.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, 15, @.on
> > exec sp_trace_setevent @.TraceID, 43, 21, @.on
> > exec sp_trace_setevent @.TraceID, 43, 26, @.on
> > exec sp_trace_setevent @.TraceID, 43, 35, @.on
> >
> > --++++++++++++++++++++++++++++++++++++++
> > --Capture SQL:BatchCompleted
> > --++++++++++++++++++++++++++++++++++++++
> > exec sp_trace_setevent @.TraceID, 12, 1, @.on
> > exec sp_trace_setevent @.TraceID, 12, 2, @.on
> > exec sp_trace_setevent @.TraceID, 12, 3, @.on
> > exec sp_trace_setevent @.TraceID, 12, 8, @.on
> > exec sp_trace_setevent @.TraceID, 12, 9, @.on
> > exec sp_trace_setevent @.TraceID, 12, 10, @.on
> > exec sp_trace_setevent @.TraceID, 12, 11, @.on
> > exec sp_trace_setevent @.TraceID, 12, 12, @.on
> > exec sp_trace_setevent @.TraceID, 12, 13, @.on
> > exec sp_trace_setevent @.TraceID, 12, 14, @.on
> > exec sp_trace_setevent @.TraceID, 12, 15, @.on
> > exec sp_trace_setevent @.TraceID, 12, 21, @.on
> > exec sp_trace_setevent @.TraceID, 12, 26, @.on
> > exec sp_trace_setevent @.TraceID, 12, 35, @.on
> >
> > --++++++++++++++++++++++++++++++++++++++
> > -- Capture Generic Information
> > --++++++++++++++++++++++++++++++++++++++
> > exec sp_trace_setevent @.TraceID, 70, 1, @.on
> > exec sp_trace_setevent @.TraceID, 70, 2, @.on
> > exec sp_trace_setevent @.TraceID, 70, 3, @.on
> > exec sp_trace_setevent @.TraceID, 70, 8, @.on
> > exec sp_trace_setevent @.TraceID, 70, 9, @.on
> > exec sp_trace_setevent @.TraceID, 70, 10, @.on
> > exec sp_trace_setevent @.TraceID, 70, 11, @.on
> > exec sp_trace_setevent @.TraceID, 70, 12, @.on
> > exec sp_trace_setevent @.TraceID, 70, 14, @.on
> > exec sp_trace_setevent @.TraceID, 70, 21, @.on
> > exec sp_trace_setevent @.TraceID, 70, 26, @.on
> > exec sp_trace_setevent @.TraceID, 70, 35, @.on
> > exec sp_trace_setevent @.TraceID, 71, 1, @.on
> > exec sp_trace_setevent @.TraceID, 71, 2, @.on
> > exec sp_trace_setevent @.TraceID, 71, 3, @.on
> > exec sp_trace_setevent @.TraceID, 71, 8, @.on
> > exec sp_trace_setevent @.TraceID, 71, 9, @.on
> > exec sp_trace_setevent @.TraceID, 71, 10, @.on
> > exec sp_trace_setevent @.TraceID, 71, 11, @.on
> > exec sp_trace_setevent @.TraceID, 71, 12, @.on
> > exec sp_trace_setevent @.TraceID, 71, 14, @.on
> > exec sp_trace_setevent @.TraceID, 71, 21, @.on
> > exec sp_trace_setevent @.TraceID, 71, 26, @.on
> > exec sp_trace_setevent @.TraceID, 71, 35, @.on
> > exec sp_trace_setevent @.TraceID, 72, 1, @.on
> > exec sp_trace_setevent @.TraceID, 72, 2, @.on
> > exec sp_trace_setevent @.TraceID, 72, 3, @.on
> > exec sp_trace_setevent @.TraceID, 72, 8, @.on
> > exec sp_trace_setevent @.TraceID, 72, 9, @.on
> > exec sp_trace_setevent @.TraceID, 72, 10, @.on
> > exec sp_trace_setevent @.TraceID, 72, 11, @.on
> > exec sp_trace_setevent @.TraceID, 72, 12, @.on
> > exec sp_trace_setevent @.TraceID, 72, 14, @.on
> > exec sp_trace_setevent @.TraceID, 72, 21, @.on
> > exec sp_trace_setevent @.TraceID, 72, 26, @.on
> > exec sp_trace_setevent @.TraceID, 72, 35, @.on
> > exec sp_trace_setevent @.TraceID, 74, 1, @.on
> > exec sp_trace_setevent @.TraceID, 74, 2, @.on
> > exec sp_trace_setevent @.TraceID, 74, 3, @.on
> > exec sp_trace_setevent @.TraceID, 74, 8, @.on
> > exec sp_trace_setevent @.TraceID, 74, 9, @.on
> > exec sp_trace_setevent @.TraceID, 74, 10, @.on
> > exec sp_trace_setevent @.TraceID, 74, 11, @.on
> > exec sp_trace_setevent @.TraceID, 74, 12, @.on
> > exec sp_trace_setevent @.TraceID, 74, 14, @.on
> > exec sp_trace_setevent @.TraceID, 74, 21, @.on
> > exec sp_trace_setevent @.TraceID, 74, 26, @.on
> > exec sp_trace_setevent @.TraceID, 74, 35, @.on
> > exec sp_trace_setevent @.TraceID, 77, 1, @.on
> > exec sp_trace_setevent @.TraceID, 77, 2, @.on
> > exec sp_trace_setevent @.TraceID, 77, 3, @.on
> > exec sp_trace_setevent @.TraceID, 77, 8, @.on
> > exec sp_trace_setevent @.TraceID, 77, 9, @.on
> > exec sp_trace_setevent @.TraceID, 77, 10, @.on
> > exec sp_trace_setevent @.TraceID, 77, 11, @.on
> > exec sp_trace_setevent @.TraceID, 77, 12, @.on
> > exec sp_trace_setevent @.TraceID, 77, 14, @.on
> > exec sp_trace_setevent @.TraceID, 77, 21, @.on
> > exec sp_trace_setevent @.TraceID, 77, 26, @.on
> > exec sp_trace_setevent @.TraceID, 77, 35, @.on
> > exec sp_trace_setevent @.TraceID, 78, 1, @.on
> > exec sp_trace_setevent @.TraceID, 78, 2, @.on
> > exec sp_trace_setevent @.TraceID, 78, 3, @.on
> > exec sp_trace_setevent @.TraceID, 78, 8, @.on
> > exec sp_trace_setevent @.TraceID, 78, 9, @.on
> > exec sp_trace_setevent @.TraceID, 78, 10, @.on
> > exec sp_trace_setevent @.TraceID, 78, 11, @.on
> > exec sp_trace_setevent @.TraceID, 78, 12, @.on
> > exec sp_trace_setevent @.TraceID, 78, 14, @.on
> > exec sp_trace_setevent @.TraceID, 78, 21, @.on
> > exec sp_trace_setevent @.TraceID, 78, 26, @.on
> > exec sp_trace_setevent @.TraceID, 78, 35, @.on
> > exec sp_trace_setevent @.TraceID, 100, 1, @.on
> > exec sp_trace_setevent @.TraceID, 100, 2, @.on
> > exec sp_trace_setevent @.TraceID, 100, 3, @.on
> > exec sp_trace_setevent @.TraceID, 100, 8, @.on
> > exec sp_trace_setevent @.TraceID, 100, 9, @.on
> > exec sp_trace_setevent @.TraceID, 100, 10, @.on
> > exec sp_trace_setevent @.TraceID, 100, 11, @.on
> > exec sp_trace_setevent @.TraceID, 100, 12, @.on
> > exec sp_trace_setevent @.TraceID, 100, 14, @.on
> > exec sp_trace_setevent @.TraceID, 100, 21, @.on
> > exec sp_trace_setevent @.TraceID, 100, 26, @.on
> > exec sp_trace_setevent @.TraceID, 100, 35, @.on
> >
> > -- Set the Filters
> > declare @.intfilter int
> > declare @.bigintfilter bigint
> >
> > -- set database filter if @.dbname is not null
> > if @.dbname is not null
> > begin
> > -- check @.dbname is on the server
> > if (select db_id(@.dbname)) is not null
> > set @.intfilter = db_id(@.dbname)
> > exec sp_trace_setfilter @.TraceID, 3, 1, 0, @.intfilter
> > end
> >
> > exec sp_trace_setfilter @.TraceID, 10, 0, 7, N'SQL Profiler'
> >
> > -- set filter for Long Running Query(LRQ) Duration
> > -- Added this 22/12/2004
> > select @.bigintfilter = (@.QueryDuration*1000) -- need to set this in
> > milliseconds(ms)
> > exec sp_trace_setfilter @.TraceID, 13, 0, 4, @.bigintfilter
> >
> >
> > -- 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:
> > -- display Trace information (can output this to a logfile in sqlagent)
> > select * from ::fn_trace_getinfo(default)
> >
> > -- clear down temp table
> > if (select object_id('tempdb.dbo.##fileexists') from
> > tempdb.dbo.sysobjects(nolock) where name = '##fileexists') is not null
> > drop table ##fileexists
> > GO
> >
> > "Wanderer" <Wanderer@.discussions.microsoft.com> wrote in message
> > news:E8FD0981-2E9F-4D42-B489-304E8AE57E16@.microsoft.com...
> > > Yes - @.DurationFilter needs to be BIGINT, and is - I ran with INT
before,
> > and
> > > got error message saying that it needed to be BIGINT.
> > >
> > > "Server: Msg 214, Level 16, State 3, Procedure sp_trace_setfilter,
Line
> > 272
> > > Procedure expects parameter '@.value' of type 'bigint'."
> > >
> > > "Olu Adedeji" wrote:
> > >
> > > > 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. @.DurationFilterValue BIGINT = 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 <> 0 Begin if @.ReturnCode = 1 Print 'Error 1 - Unknown
> > > > > error.' if @.ReturnCode = 10 Print 'Error 10 - Invalid options.
> > Returned when
> > > > > options specified are incompatible.' if @.ReturnCode = 12 Print
'Error
> > 12 -
> > > > > Cannot create tracefile - check if file already exists, or this
trace
> > already
> > > > > running' if @.ReturnCode = 13 Print 'Error 13 - Out of memory.
Returned
> > when
> > > > > there is not enough memory to perform the specified action.' if
> > @.ReturnCode => > > > > 14 Print 'Error 14 - Invalid stop time. Returned when the stop
time
> > > > > specified has already happened.' if @.ReturnCode = 15 Print 'Error
15 -
> > > > > Invalid parameters. Returned when the user supplied incompatible
> > > > > parameters.' else Print '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 <> 0 Begin if @.ReturnCode = 13
Print
> > > > > 'ERROR 13 - Out of memory. Returned when there is not enough
memory to
> > > > > perform the specified action.' else if @.ReturnCode = 9 Print
'ERROR
> > 9 -
> > > > > The specified Trace Handle is not valid.' else if @.ReturnCode = 8
> > print
> > > > > 'ERROR 8 - The specified Status is not valid.' else Print 'ERROR
1 -
> > Unknown
> > > > > Error' GoTo ErrorHandlerendexec sp_trace_setfilter
> > > > >
@.TraceId,@.ColumnId,@.LogicalOperator,@.ComparisonOperator,@.Valueselect
> > > > > @.ReturnCode=@.@.Errorif @.ReturnCode <> 0 Begin if @.ReturnCode = 1
print
> > 'ERROR
> > > > > 1 - Unknown error.' else if @.ReturnCode = 2 Print 'ERROR 2 - The
trace
> > is
> > > > > currently running. Changing the trace at this time will result in
an
> > > > > error.' else if @.ReturnCode = 4 Print 'ERROR 4 - The specified
Column
> > is not
> > > > > valid.' else if @.ReturnCode = 5 print 'ERROR 5 - The specified
Column
> > is not
> > > > > allowed for filtering.' else if @.ReturnCode = 6 print 'ERROR 6 -
The
> > > > > specified Comparison Operator is not valid. ' else if @.ReturnCode
= 7
> > print
> > > > > 'ERROR 7 - The specified Logical Operator is not valid.' else if
> > @.ReturnCode
> > > > > = 9 print 'ERROR 9 - The specified Trace Handle is not valid.'
else if
> > > > > @.ReturnCode = 13 print 'ERROR 13 - Out of memory. Returned when
there
> > is not
> > > > > enough memory to perform the specified action.' else if
@.ReturnCode => > > > > 16 print 'ERROR 16 - The function is not valid for this trace.'
else
> > Print
> > > > > 'ERROR x - Unknown Error' GoTo ErrorHandlerendif
@.DurationFilterValue
> > <>
> > > > > 0 begin set @.columnId = 13 --Duration set @.ComparisonOperator = 2
> > > > > --Greater than exec sp_trace_setfilter
> > > > >
> >
@.TraceId,@.ColumnId,@.LogicalOperator,@.ComparisonOperator,@.DurationFilterValue
> > select
> > > > > @.ReturnCode=@.@.Error if @.ReturnCode <> 0 Begin if @.ReturnCode = 1
print
> > > > > 'ERROR 1 - Unknown error.' else if @.ReturnCode = 2 Print 'ERROR
2 -
> > The
> > > > > trace is currently running. Changing the trace at this time will
> > result in an
> > > > > error.' else if @.ReturnCode = 4 Print 'ERROR 4 - The specified
Column
> > is
> > > > > not valid.' else if @.ReturnCode = 5 print 'ERROR 5 - The specified
> > Column
> > > > > is not allowed for filtering.' else if @.ReturnCode = 6 print
'ERROR
> > 6 -
> > > > > The specified Comparison Operator is not valid. ' else if
@.ReturnCode
> > => > > > > 7 print 'ERROR 7 - The specified Logical Operator is not valid.'
else
> > if
> > > > > @.ReturnCode = 9 print 'ERROR 9 - The specified Trace Handle is not
> > > > > valid.' else if @.ReturnCode = 13 print 'ERROR 13 - Out of memory.
> > > > > Returned when there is not enough memory to perform the specified
> > > > > action.' else if @.ReturnCode = 16 print 'ERROR 16 - The function
is
> > not
> > > > > valid for this trace.' else Print 'ERROR x - Unknown Error' GoTo
> > > > > ErrorHandler endendErrorHandler:Return @.ReturnCode
> > > > >
> > > > >
> > > > > The Code that calls the trace
> > > > >
> > > > > DECLARE @.FileName NVARCHAR(128)DECLARE @.RunStopTime
DATETIMEDECLARE
> > > > > @.MaxSize BIGINTDECLARE @.DurationFilter BIGINTDECLARE
> > > > > @.TraceID INTEGERDECLARE @.MinutesToRun INTEGERDECLARE @.RC
> > INTEGERDECLARE
> > > > > @.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,@.TraceID
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...
> > > > >
> >
> >
> >|||Thanks Olu, for the responses - just realized I hadn't been polite enough to
acknowledge them!
I must have mis-understood yuo last mail - I thought you were proposing I
use your SP, or check how you are using the SP_TRACE_SETFILTER.
The specific Traces I am trying to turn on are:
set @.LogicalOperator = 0 --and (1 = OR)
set @.columnId = 13 --Duration
set @.ComparisonOperator = 2 --Greater than
@.DurationFilterValue - I am passing in 1000, i.e. 1 second
I see you are using comparison operator of 4 - greater than or equal to - I
will try that and see if that is somehopw causing the issue. If not, I will
create the trace via profiler, and then try and gen the trace and see what's
what.
Thanks for the help so far!!!
"Olu Adedeji" wrote:
> Wanderer,
> as per my email, I was intimating that you open profiler and set the filter
> manually, then script out the trace and cut the section that performs the
> sp_trace_setfilter into your procedure substituting your parameters for
> hardcoded values. then run your procedure and check the trc files to see if
> the traces have been set. . from your post it is obvious that profiler does
> not understand the parameters you have passed into sp_trace_setfilter and as
> a result it is unable to present it in the trc file. PS. out of interest
> what are you trying to filter?
>
> PS. I don't dispute that your trace runs fine . . I am merely trying to
> help resolve the problem
>|||Hmmm- there looks like mine:
--Profiler's trace
-- Set the Filters
declare @.intfilter int
declare @.bigintfilter bigint
exec sp_trace_setfilter @.TraceID, 10, 0, 7, N'SQL Profiler'
set @.bigintfilter = 1000
exec sp_trace_setfilter @.TraceID, 13, 0, 4, @.bigintfilter
set @.intfilter = 100
exec sp_trace_setfilter @.TraceID, 22, 0, 4, @.intfilter
--My trace
set @.LogicalOperator = 0
set @.columnId = 13
set @.ComparisonOperator = 4
@.DurationFilterValue = 1000 (bigint)
exec sp_trace_setfilter
@.TraceId,@.ColumnId,@.LogicalOperator,@.ComparisonOperator,@.DurationFilterValue
which is effectively
exec sp_trace_setfilter @.tracid,13,0,4,@.DurationFilterValue
I'll see if the hardcoding works better ... sighs
... and it would seem it doesn't! Grrr
"Wanderer" wrote:
> Thanks Olu, for the responses - just realized I hadn't been polite enough to
> acknowledge them!
> I must have mis-understood yuo last mail - I thought you were proposing I
> use your SP, or check how you are using the SP_TRACE_SETFILTER.
> The specific Traces I am trying to turn on are:
> set @.LogicalOperator = 0 --and (1 = OR)
> set @.columnId = 13 --Duration
> set @.ComparisonOperator = 2 --Greater than
> @.DurationFilterValue - I am passing in 1000, i.e. 1 second
> I see you are using comparison operator of 4 - greater than or equal to - I
> will try that and see if that is somehopw causing the issue. If not, I will
> create the trace via profiler, and then try and gen the trace and see what's
> what.
> Thanks for the help so far!!!
> "Olu Adedeji" wrote:
> > Wanderer,
> >
> > as per my email, I was intimating that you open profiler and set the filter
> > manually, then script out the trace and cut the section that performs the
> > sp_trace_setfilter into your procedure substituting your parameters for
> > hardcoded values. then run your procedure and check the trc files to see if
> > the traces have been set. . from your post it is obvious that profiler does
> > not understand the parameters you have passed into sp_trace_setfilter and as
> > a result it is unable to present it in the trc file. PS. out of interest
> > what are you trying to filter?
> >
> >
> > PS. I don't dispute that your trace runs fine . . I am merely trying to
> > help resolve the problem
> >
>|||sure thing! I hope that works. . .
"Wanderer" <Wanderer@.discussions.microsoft.com> wrote in message
news:E57DE62A-122D-448E-BEFB-AE3DDEE2F877@.microsoft.com...
> Hmmm- there looks like mine:
> --Profiler's trace
> -- Set the Filters
> declare @.intfilter int
> declare @.bigintfilter bigint
> exec sp_trace_setfilter @.TraceID, 10, 0, 7, N'SQL Profiler'
> set @.bigintfilter = 1000
> exec sp_trace_setfilter @.TraceID, 13, 0, 4, @.bigintfilter
> set @.intfilter = 100
> exec sp_trace_setfilter @.TraceID, 22, 0, 4, @.intfilter
> --My trace
> set @.LogicalOperator = 0
> set @.columnId = 13
> set @.ComparisonOperator = 4
> @.DurationFilterValue = 1000 (bigint)
> exec sp_trace_setfilter
>
@.TraceId,@.ColumnId,@.LogicalOperator,@.ComparisonOperator,@.DurationFilterValue
> which is effectively
> exec sp_trace_setfilter @.tracid,13,0,4,@.DurationFilterValue
> I'll see if the hardcoding works better ... sighs
> ... and it would seem it doesn't! Grrr
>
> "Wanderer" wrote:
> > Thanks Olu, for the responses - just realized I hadn't been polite
enough to
> > acknowledge them!
> >
> > I must have mis-understood yuo last mail - I thought you were proposing
I
> > use your SP, or check how you are using the SP_TRACE_SETFILTER.
> >
> > The specific Traces I am trying to turn on are:
> >
> > set @.LogicalOperator = 0 --and (1 = OR)
> > set @.columnId = 13 --Duration
> > set @.ComparisonOperator = 2 --Greater than
> > @.DurationFilterValue - I am passing in 1000, i.e. 1 second
> >
> > I see you are using comparison operator of 4 - greater than or equal
to - I
> > will try that and see if that is somehopw causing the issue. If not, I
will
> > create the trace via profiler, and then try and gen the trace and see
what's
> > what.
> >
> > Thanks for the help so far!!!
> >
> > "Olu Adedeji" wrote:
> >
> > > Wanderer,
> > >
> > > as per my email, I was intimating that you open profiler and set the
filter
> > > manually, then script out the trace and cut the section that performs
the
> > > sp_trace_setfilter into your procedure substituting your parameters
for
> > > hardcoded values. then run your procedure and check the trc files to
see if
> > > the traces have been set. . from your post it is obvious that profiler
does
> > > not understand the parameters you have passed into sp_trace_setfilter
and as
> > > a result it is unable to present it in the trc file. PS. out of
interest
> > > what are you trying to filter?
> > >
> > >
> > > PS. I don't dispute that your trace runs fine . . I am merely trying
to
> > > help resolve the problem
> > >
> >|||Hmm - not working.
When I run:
SELECT * FROM ::fn_trace_getfilterinfo(1)
I get 0 rows, that implies to me that the filters are not getting created.
Can you confirm on a test that you DO get something from this?
Thanks
"Olu Adedeji" wrote:
> sure thing! I hope that works. . .
> "Wanderer" <Wanderer@.discussions.microsoft.com> wrote in message
> news:E57DE62A-122D-448E-BEFB-AE3DDEE2F877@.microsoft.com...
> > Hmmm- there looks like mine:
> > --Profiler's trace
> > -- Set the Filters
> > declare @.intfilter int
> > declare @.bigintfilter bigint
> >
> > exec sp_trace_setfilter @.TraceID, 10, 0, 7, N'SQL Profiler'
> > set @.bigintfilter = 1000
> > exec sp_trace_setfilter @.TraceID, 13, 0, 4, @.bigintfilter
> >
> > set @.intfilter = 100
> > exec sp_trace_setfilter @.TraceID, 22, 0, 4, @.intfilter
> >
> > --My trace
> > set @.LogicalOperator = 0
> > set @.columnId = 13
> > set @.ComparisonOperator = 4
> > @.DurationFilterValue = 1000 (bigint)
> > exec sp_trace_setfilter
> >
> @.TraceId,@.ColumnId,@.LogicalOperator,@.ComparisonOperator,@.DurationFilterValue
> > which is effectively
> > exec sp_trace_setfilter @.tracid,13,0,4,@.DurationFilterValue
> >
> > I'll see if the hardcoding works better ... sighs
> >
> > ... and it would seem it doesn't! Grrr
> >
> >
> >
> > "Wanderer" wrote:
> >
> > > Thanks Olu, for the responses - just realized I hadn't been polite
> enough to
> > > acknowledge them!
> > >
> > > I must have mis-understood yuo last mail - I thought you were proposing
> I
> > > use your SP, or check how you are using the SP_TRACE_SETFILTER.
> > >
> > > The specific Traces I am trying to turn on are:
> > >
> > > set @.LogicalOperator = 0 --and (1 = OR)
> > > set @.columnId = 13 --Duration
> > > set @.ComparisonOperator = 2 --Greater than
> > > @.DurationFilterValue - I am passing in 1000, i.e. 1 second
> > >
> > > I see you are using comparison operator of 4 - greater than or equal
> to - I
> > > will try that and see if that is somehopw causing the issue. If not, I
> will
> > > create the trace via profiler, and then try and gen the trace and see
> what's
> > > what.
> > >
> > > Thanks for the help so far!!!
> > >
> > > "Olu Adedeji" wrote:
> > >
> > > > Wanderer,
> > > >
> > > > as per my email, I was intimating that you open profiler and set the
> filter
> > > > manually, then script out the trace and cut the section that performs
> the
> > > > sp_trace_setfilter into your procedure substituting your parameters
> for
> > > > hardcoded values. then run your procedure and check the trc files to
> see if
> > > > the traces have been set. . from your post it is obvious that profiler
> does
> > > > not understand the parameters you have passed into sp_trace_setfilter
> and as
> > > > a result it is unable to present it in the trc file. PS. out of
> interest
> > > > what are you trying to filter?
> > > >
> > > >
> > > > PS. I don't dispute that your trace runs fine . . I am merely trying
> to
> > > > help resolve the problem
> > > >
> > >
>
>|||Ok - so it seems that, for some reason, the sp_trace_setfilter is not
working. Going to try and build in some logic to catch and check why.
Anything that you can see from my SP, that points the way, I'd appreciate
hearing it.
Have a great New Year's eve, and a peaceful and prosperous new year
"Wanderer" wrote:
> Hmm - not working.
> When I run:
> SELECT * FROM ::fn_trace_getfilterinfo(1)
> I get 0 rows, that implies to me that the filters are not getting created.
> Can you confirm on a test that you DO get something from this?
> Thanks
> "Olu Adedeji" wrote:
> > sure thing! I hope that works. . .
> >
> > "Wanderer" <Wanderer@.discussions.microsoft.com> wrote in message
> > news:E57DE62A-122D-448E-BEFB-AE3DDEE2F877@.microsoft.com...
> > > Hmmm- there looks like mine:
> > > --Profiler's trace
> > > -- Set the Filters
> > > declare @.intfilter int
> > > declare @.bigintfilter bigint
> > >
> > > exec sp_trace_setfilter @.TraceID, 10, 0, 7, N'SQL Profiler'
> > > set @.bigintfilter = 1000
> > > exec sp_trace_setfilter @.TraceID, 13, 0, 4, @.bigintfilter
> > >
> > > set @.intfilter = 100
> > > exec sp_trace_setfilter @.TraceID, 22, 0, 4, @.intfilter
> > >
> > > --My trace
> > > set @.LogicalOperator = 0
> > > set @.columnId = 13
> > > set @.ComparisonOperator = 4
> > > @.DurationFilterValue = 1000 (bigint)
> > > exec sp_trace_setfilter
> > >
> > @.TraceId,@.ColumnId,@.LogicalOperator,@.ComparisonOperator,@.DurationFilterValue
> > > which is effectively
> > > exec sp_trace_setfilter @.tracid,13,0,4,@.DurationFilterValue
> > >
> > > I'll see if the hardcoding works better ... sighs
> > >
> > > ... and it would seem it doesn't! Grrr
> > >
> > >
> > >
> > > "Wanderer" wrote:
> > >
> > > > Thanks Olu, for the responses - just realized I hadn't been polite
> > enough to
> > > > acknowledge them!
> > > >
> > > > I must have mis-understood yuo last mail - I thought you were proposing
> > I
> > > > use your SP, or check how you are using the SP_TRACE_SETFILTER.
> > > >
> > > > The specific Traces I am trying to turn on are:
> > > >
> > > > set @.LogicalOperator = 0 --and (1 = OR)
> > > > set @.columnId = 13 --Duration
> > > > set @.ComparisonOperator = 2 --Greater than
> > > > @.DurationFilterValue - I am passing in 1000, i.e. 1 second
> > > >
> > > > I see you are using comparison operator of 4 - greater than or equal
> > to - I
> > > > will try that and see if that is somehopw causing the issue. If not, I
> > will
> > > > create the trace via profiler, and then try and gen the trace and see
> > what's
> > > > what.
> > > >
> > > > Thanks for the help so far!!!
> > > >
> > > > "Olu Adedeji" wrote:
> > > >
> > > > > Wanderer,
> > > > >
> > > > > as per my email, I was intimating that you open profiler and set the
> > filter
> > > > > manually, then script out the trace and cut the section that performs
> > the
> > > > > sp_trace_setfilter into your procedure substituting your parameters
> > for
> > > > > hardcoded values. then run your procedure and check the trc files to
> > see if
> > > > > the traces have been set. . from your post it is obvious that profiler
> > does
> > > > > not understand the parameters you have passed into sp_trace_setfilter
> > and as
> > > > > a result it is unable to present it in the trc file. PS. out of
> > interest
> > > > > what are you trying to filter?
> > > > >
> > > > >
> > > > > PS. I don't dispute that your trace runs fine . . I am merely trying
> > to
> > > > > help resolve the problem
> > > > >
> > > >
> >
> >
> >|||Wanderer wrote:
> Ok - so it seems that, for some reason, the sp_trace_setfilter is not
> working. Going to try and build in some logic to catch and check why.
> Anything that you can see from my SP, that points the way, I'd
> appreciate hearing it.
>
You wouldn't see the filter in Profiler if you opened a TRC file after a
server-side trace. All you see in Profiler is the events captured during
the trace. The other information (filters) are not saved anywhere in the
trace file.
Also, I assume when you ran the following statement, you knew that the
running trace had a handle of 1? It seems unlikely that the handle would
be 1 unless you just cycled the server.
SELECT * FROM ::fn_trace_getfilterinfo(1)
I can't read your script file because of the wrapping. What events did
you capture that are not showing the filter applied?
--
David Gugick
Imceda Software
www.imceda.com|||Hi David, thanks for the response!
Thanks for the heads-up on not being able to see the traces! Learnt
something new, there. The problem is I am attempteing to Filter on duration >
1000, but seem to be getting everything. I really don't want to run in our
production environment and capture everything, I strongly suspect that I will
end up with GB's of tracefiles.
Regards the " SELECT * FROM ::fn_trace_getfilterinfo(1)", I also used:
SELECT * FROM ::fn_trace_getfilterinfo(@.TraceId)
where @.TraceId was the @.TraceID variable used to assign the traces in the
SP, and is an output variable. And still got 0 rows returned.
Here is how I called the SP, and then checked the settings:
--Create trace, writing out to tracefile, until endtime
exec @.Rc=Up_Auto_Profiler_Perf
@.FileName,@.RunStopTime,@.MaxSize,@.DurationFilter,@.TraceID output
--Error Handling
select @.rc
select @.TraceID
SELECT * FROM ::fn_trace_getfilterinfo(1)
SELECT * FROM ::fn_trace_getfilterinfo(default)
SELECT * FROM ::fn_trace_getfilterinfo(@.TraceID)
select * from ::fn_trace_getinfo(1)
select * from ::fn_trace_getinfo(default)
select * from ::fn_trace_getinfo(@.TraceID)
The 3 ::fn_trace_getfilterinfo select ALL return 0 rows, whereas the 3
::fn_trace_getinfo ALL return data about the trace I had just created!
I stringly suspect that either I have somehow failed to exec the
SP_TRACE_SETFILTER (and when debugging it certainly didn't seem like a
problem), or it failed (and I do do fairly complete error checking, per the
error codes listed in the BOL), or else, for some reason, it is just not
being applied.
"David Gugick" wrote:
> Wanderer wrote:
> > Ok - so it seems that, for some reason, the sp_trace_setfilter is not
> > working. Going to try and build in some logic to catch and check why.
> > Anything that you can see from my SP, that points the way, I'd
> > appreciate hearing it.
> >
> You wouldn't see the filter in Profiler if you opened a TRC file after a
> server-side trace. All you see in Profiler is the events captured during
> the trace. The other information (filters) are not saved anywhere in the
> trace file.
> Also, I assume when you ran the following statement, you knew that the
> running trace had a handle of 1? It seems unlikely that the handle would
> be 1 unless you just cycled the server.
> SELECT * FROM ::fn_trace_getfilterinfo(1)
> I can't read your script file because of the wrapping. What events did
> you capture that are not showing the filter applied?
> --
> David Gugick
> Imceda Software
> www.imceda.com
>|||Well, I've tried to add steps to check on the adding of the filters, and all
i can say is - it doesn't seem to work!
Here is a section of the code (hopefully the wordwrap doesn't make it
completely illegible).
****code****
exec sp_trace_setfilter
@.TraceId,@.ColumnId,@.LogicalOperator,@.ComparisonOperator,@.DurationFilterValue
select @.ReturnCode=@.@.Error
Print 'Trace Filter 2 set'
if @.ReturnCode <> 0
Begin
if @.ReturnCode = 1
print 'ERROR 1 - Unknown error.'
else
if @.ReturnCode = 2
Print 'ERROR 2 - The trace is currently running. Changing the trace at
this time will result in an error.'
else
if @.ReturnCode = 4
Print 'ERROR 4 - The specified Column is not valid.'
else
if @.ReturnCode = 5
print 'ERROR 5 - The specified Column is not allowed for filtering.'
else
if @.ReturnCode = 6
print 'ERROR 6 - The specified Comparison Operator is not valid. '
else
if @.ReturnCode = 7
print 'ERROR 7 - The specified Logical Operator is not valid.'
else
if @.ReturnCode = 9
print 'ERROR 9 - The specified Trace Handle is not valid.'
else
if @.ReturnCode = 13
print 'ERROR 13 - Out of memory. Returned when there is not enough memory
to perform the specified action.'
else
if @.ReturnCode = 16
print 'ERROR 16 - The function is not valid for this trace.'
else
Print 'ERROR x - Unknown Error'
GoTo ErrorHandler
end
SELECT * FROM ::fn_trace_getfilterinfo(@.TraceID)
****code****
I get NO response that an error has occurred, the "print's" confirm that the
code stepped through, yet the ::fn_trace_setfilter returns NO ROWS!
"David Gugick" wrote:
> Wanderer wrote:
> > Ok - so it seems that, for some reason, the sp_trace_setfilter is not
> > working. Going to try and build in some logic to catch and check why.
> > Anything that you can see from my SP, that points the way, I'd
> > appreciate hearing it.
> >
> You wouldn't see the filter in Profiler if you opened a TRC file after a
> server-side trace. All you see in Profiler is the events captured during
> the trace. The other information (filters) are not saved anywhere in the
> trace file.
> Also, I assume when you ran the following statement, you knew that the
> running trace had a handle of 1? It seems unlikely that the handle would
> be 1 unless you just cycled the server.
> SELECT * FROM ::fn_trace_getfilterinfo(1)
> I can't read your script file because of the wrapping. What events did
> you capture that are not showing the filter applied?
> --
> David Gugick
> Imceda Software
> www.imceda.com
>|||Found it!!!
*sigh* - a blonde moment!
Just before I add the filters, I exec:
exec sp_trace_setstatus @.TraceId,1
In other words, I start the trace, hence I cannot apply filters. And because
I am checing @.@.error for the exec sp_trace_setfilter, and not doing what I
SHOULD be doing, which is:
exec @.ReturnCode=sp_trace_setfilter ...
I never received the response saying that the filter was running (error 2).
Thanks for your help !
"David Gugick" wrote:
> Wanderer wrote:
> > Ok - so it seems that, for some reason, the sp_trace_setfilter is not
> > working. Going to try and build in some logic to catch and check why.
> > Anything that you can see from my SP, that points the way, I'd
> > appreciate hearing it.
> >
> You wouldn't see the filter in Profiler if you opened a TRC file after a
> server-side trace. All you see in Profiler is the events captured during
> the trace. The other information (filters) are not saved anywhere in the
> trace file.
> Also, I assume when you ran the following statement, you knew that the
> running trace had a handle of 1? It seems unlikely that the handle would
> be 1 unless you just cycled the server.
> SELECT * FROM ::fn_trace_getfilterinfo(1)
> I can't read your script file because of the wrapping. What events did
> you capture that are not showing the filter applied?
> --
> David Gugick
> Imceda Software
> www.imceda.com
>|||OH, btw,
I found that after properly creating those filters, when I open the trace
file created by my SP, in profiler, I DO see the filters under the properties
tab for the trace.
Just FYI
"David Gugick" wrote:
> Wanderer wrote:
> > Ok - so it seems that, for some reason, the sp_trace_setfilter is not
> > working. Going to try and build in some logic to catch and check why.
> > Anything that you can see from my SP, that points the way, I'd
> > appreciate hearing it.
> >
> You wouldn't see the filter in Profiler if you opened a TRC file after a
> server-side trace. All you see in Profiler is the events captured during
> the trace. The other information (filters) are not saved anywhere in the
> trace file.
> Also, I assume when you ran the following statement, you knew that the
> running trace had a handle of 1? It seems unlikely that the handle would
> be 1 unless you just cycled the server.
> SELECT * FROM ::fn_trace_getfilterinfo(1)
> I can't read your script file because of the wrapping. What events did
> you capture that are not showing the filter applied?
> --
> David Gugick
> Imceda Software
> www.imceda.com
>|||Wanderer wrote:
> OH, btw,
> I found that after properly creating those filters, when I open the
> trace file created by my SP, in profiler, I DO see the filters under
> the properties tab for the trace.
> Just FYI
>
Strange. I don't see them on some test traces I just ran. Are you saying
that you run a server-side trace, stop the trace, open the trace file in
Profiler, and then see the trace filters? I can't see it here using that
method.
Any verification appreciated.
Thanks
David Gugick|||Hi David,
Using that SP I created, and then fixed :-), I basically run a server side
trace to file. Then I copy the file to another machine, where I do my
analysis.
When opening that trace file, in Profiler, and going to the properties of
the trace, I definitely see my filter.
HTH
"David Gugick" wrote:
> Wanderer wrote:
> > OH, btw,
> >
> > I found that after properly creating those filters, when I open the
> > trace file created by my SP, in profiler, I DO see the filters under
> > the properties tab for the trace.
> >
> > Just FYI
> >
> Strange. I don't see them on some test traces I just ran. Are you saying
> that you run a server-side trace, stop the trace, open the trace file in
> Profiler, and then see the trace filters? I can't see it here using that
> method.
> Any verification appreciated.
> Thanks
>
> --
> David Gugick
>|||Wanderer wrote:
> Hi David,
> Using that SP I created, and then fixed :-), I basically run a server
> side trace to file. Then I copy the file to another machine, where I
> do my analysis.
> When opening that trace file, in Profiler, and going to the
> properties of the trace, I definitely see my filter.
> HTH
> "David Gugick" wrote:
>> Wanderer wrote:
>> OH, btw,
>> I found that after properly creating those filters, when I open the
>> trace file created by my SP, in profiler, I DO see the filters under
>> the properties tab for the trace.
>> Just FYI
>>
>> Strange. I don't see them on some test traces I just ran. Are you
>> saying that you run a server-side trace, stop the trace, open the
>> trace file in Profiler, and then see the trace filters? I can't see
>> it here using that method.
>> Any verification appreciated.
>> Thanks
>>
>> --
>> David Gugick
Can you repost your code so I can see if there are any differences?
--
David Gugick|||Sure. here goes:
****code****
if exists (select 1 from sysobjects where name = 'Up_Auto_Profiler_Perf')
drop procedure Up_Auto_Profiler_Perf
go
create 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.
@.DurationFilterValue BIGINT, --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.
-- version 1.3 - Regan Galbraith 2005-01-03
-- Fixed the problems with applying trace filters by correcting error
checking to
-- use return code, and by moving start trace to end, sicne filters
cannot apply
-- to running trace.
--
AS
set nocount on
--Declare Control Variable
declare @.ReturnCode Int
--Declare Option Variables
declare @.Option int
declare @.EventId int
declare @.On bit
declare @.Value int
declare @.ComparisonOperator int
declare @.ColumnId int
declare @.LogicalOperator int
--Set Option Variable
set @.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 > 100
set @.Value = 100
set @.ColumnId = 22 --0bjectid
set @.LogicalOperator = 0 --and (1 = OR)
set @.ComparisonOperator = 2 --Greater than
--Set Control Variables
set @.ReturnCode = 0 --No Error
set @.On = 1 --True
--Create a trace, retrieve @.TraceId
exec @.ReturnCode=sp_trace_create @.TraceId
output,@.Option,@.TraceFile,@.MaxSize,@.StopTime
if @.ReturnCode <> 0
Begin
if @.ReturnCode = 1
Print 'Error 1 - Unknown error.'
if @.ReturnCode = 10
Print 'Error 10 - Invalid options. Returned when options specified are
incompatible.'
if @.ReturnCode = 12
Print 'Error 12 - Cannot create tracefile - check if file already exists,
or this trace already running'
if @.ReturnCode = 13
Print 'Error 13 - Out of memory. Returned when there is not enough memory
to perform the specified action.'
if @.ReturnCode = 14
Print 'Error 14 - Invalid stop time. Returned when the stop time specified
has already happened.'
if @.ReturnCode = 15
Print 'Error 15 - Invalid parameters. Returned when the user supplied
incompatible parameters.'
else
Print 'Unexpected and Unknown error In creating trace - Please review'
Goto ErrorHandler
End
--Populate Trace with Events
--SECTION CURSORS
-- this set is : cursor execute
set @.EventId = 74
exec sp_trace_setevent @.TraceId,@.EventId,27,@.On --EventClass
exec sp_trace_setevent @.TraceId,@.EventId,25,@.On --IntegerData
-- this set is : Cursor Open
set @.EventId = 53
exec sp_trace_setevent @.TraceId,@.EventId,27,@.On --EventClass
exec sp_trace_setevent @.TraceId,@.EventId,25,@.On --IntegerData
-- this set is : Cursor Recompile
set @.EventId = 75
exec sp_trace_setevent @.TraceId,@.EventId,27,@.On --EventClass
--SECTION ERRORS AND WARNINGS
-- this set is : MissingJoinPredicate
set @.EventId = 80
exec sp_trace_setevent @.TraceId,@.EventId,27,@.On --EventClass
--SECTION LOCKS
-- this set is : Lock:DeadLock
set @.EventId = 25
exec sp_trace_setevent @.TraceId,@.EventId,2,@.On --BinaryData
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,24,@.On --indexID
exec sp_trace_setevent @.TraceId,@.EventId,25,@.On --IntegerData
exec sp_trace_setevent @.TraceId,@.EventId,32,@.On --Mode
exec sp_trace_setevent @.TraceId,@.EventId,22,@.On --ObjectID
-- this set is : Lock:DeadLockChain
set @.EventId = 59
exec sp_trace_setevent @.TraceId,@.EventId,2,@.On --BinaryData
exec sp_trace_setevent @.TraceId,@.EventId,27,@.On --EventClass
exec sp_trace_setevent @.TraceId,@.EventId,24,@.On --indexID
exec sp_trace_setevent @.TraceId,@.EventId,25,@.On --IntegerData
exec sp_trace_setevent @.TraceId,@.EventId,32,@.On --Mode
exec sp_trace_setevent @.TraceId,@.EventId,22,@.On --ObjectID
-- this set is : Lock:Timeout
set @.EventId = 27
exec sp_trace_setevent @.TraceId,@.EventId,2,@.On --BinaryData
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,24,@.On --indexID
exec sp_trace_setevent @.TraceId,@.EventId,32,@.On --Mode
exec 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 Plan
-- set @.EventId = 97
-- 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 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:Recompile
set @.EventId = 37
exec sp_trace_setevent @.TraceId,@.EventId,27,@.On --EventClass
exec sp_trace_setevent @.TraceId,@.EventId,29,@.On --NestLevel
exec sp_trace_setevent @.TraceId,@.EventId,22,@.On --ObjectID
exec sp_trace_setevent @.TraceId,@.EventId,34,@.On --ObjectName
exec sp_trace_setevent @.TraceId,@.EventId,1,@.On --TextData
--this set is SP:RPC:Completed
set @.EventId = 10
exec sp_trace_setevent @.TraceId,@.EventId,27,@.On --EventClass
exec sp_trace_setevent @.TraceId,@.EventId,13,@.On --duration
exec sp_trace_setevent @.TraceId,@.EventId,18,@.On --cpu
exec sp_trace_setevent @.TraceId,@.EventId,15,@.On --EndTime
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 --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 BatchCompleted
set @.EventId = 12
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,16,@.On --Reads
exec sp_trace_setevent @.TraceId,@.EventId,1,@.On --TextData
exec 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 --Writes
--Add a trace filter to exclude System Ids - that is ObjectId > 100
exec @.ReturnCode=sp_trace_setfilter
@.TraceId,@.ColumnId,@.LogicalOperator,@.ComparisonOperator,@.Value
if @.ReturnCode <> 0
Begin
if @.ReturnCode = 1
print 'ERROR 1 - Unknown error.'
else
if @.ReturnCode = 2
Print 'ERROR 2 - The trace is currently running. Changing the trace at
this time will result in an error.'
else
if @.ReturnCode = 4
Print 'ERROR 4 - The specified Column is not valid.'
else
if @.ReturnCode = 5
print 'ERROR 5 - The specified Column is not allowed for filtering.'
else
if @.ReturnCode = 6
print 'ERROR 6 - The specified Comparison Operator is not valid. '
else
if @.ReturnCode = 7
print 'ERROR 7 - The specified Logical Operator is not valid.'
else
if @.ReturnCode = 9
print 'ERROR 9 - The specified Trace Handle is not valid.'
else
if @.ReturnCode = 13
print 'ERROR 13 - Out of memory. Returned when there is not enough memory
to perform the specified action.'
else
if @.ReturnCode = 16
print 'ERROR 16 - The function is not valid for this trace.'
else
Print 'ERROR x - Unknown Error'
GoTo ErrorHandler
end
if @.DurationFilterValue <> 0
begin
set @.columnId = 13 --Duration
set @.ComparisonOperator = 4 --Greater than or equal to
--Add trace for Trace being create (@.TraceID, on column Duration, Greater
than Equal to, @.DurationFilterValue)
exec @.ReturnCode=sp_trace_setfilter
@.TraceId,@.ColumnId,@.LogicalOperator,@.ComparisonOperator,@.DurationFilterValue
if @.ReturnCode <> 0
Begin
if @.ReturnCode = 1
print 'ERROR 1 - Unknown error.'
else
if @.ReturnCode = 2
Print 'ERROR 2 - The trace is currently running. Changing the trace at
this time will result in an error.'
else
if @.ReturnCode = 4
Print 'ERROR 4 - The specified Column is not valid.'
else
if @.ReturnCode = 5
print 'ERROR 5 - The specified Column is not allowed for filtering.'
else
if @.ReturnCode = 6
print 'ERROR 6 - The specified Comparison Operator is not valid. '
else
if @.ReturnCode = 7
print 'ERROR 7 - The specified Logical Operator is not valid.'
else
if @.ReturnCode = 9
print 'ERROR 9 - The specified Trace Handle is not valid.'
else
if @.ReturnCode = 13
print 'ERROR 13 - Out of memory. Returned when there is not enough memory
to perform the specified action.'
else
if @.ReturnCode = 16
print 'ERROR 16 - The function is not valid for this trace.'
else
Print 'ERROR x - Unknown Error'
GoTo ErrorHandler
end
end
--Some information on the trace
SELECT * FROM ::fn_trace_getfilterinfo(@.TraceID)
SELECT * FROM ::fn_trace_getinfo(@.TraceID)
Print 'Start Trace'
exec @.ReturnCode=sp_trace_setstatus @.TraceId,1
if @.ReturnCode <> 0
Begin
if @.ReturnCode = 13
Print 'ERROR 13 - Out of memory. Returned when there is not enough memory
to perform the specified action.'
else
if @.ReturnCode = 9
Print 'ERROR 9 - The specified Trace Handle is not valid.'
else
if @.ReturnCode = 8
print 'ERROR 8 - The specified Status is not valid.'
else
Print 'ERROR 1 - Unknown Error'
GoTo ErrorHandler
end
ErrorHandler:
Return @.ReturnCode
****code****
Code I use to invoke it:
****code****
set nocount on
DECLARE @.FileName NVARCHAR(128)
DECLARE @.RunStopTime DATETIME
DECLARE @.MaxSize BIGINT
DECLARE @.DurationFilter BIGINT
DECLARE @.TraceID INTEGER
DECLARE @.MinutesToRun INTEGER
DECLARE @.RC INTEGER
DECLARE @.DBID INTEGER
DECLARE @.DBNAME NVARCHAR(128)
-- Set Error Variables
SET @.DBNAME = DB_NAME()
SET @.DBID = DB_ID()
SET @.rc = 0
--Set Control Variables
set @.MinutesToRun = 5 -- this is how long the trace will run for
set @.MaxSize = 100 -- this is the maximum size for a file, in MB
set @.DurationFilter = 1000
--Set Running Variables
--the time the trace will stop
set @.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
+right(cast(datepart(d,getdate()) +100 as char(3)),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 variables
print 'File created is : '+@.FileName
print 'End time will be : '+cast(@.RunStopTime as varchar(20))
--Create trace, writing out to tracefile, until endtime
exec @.Rc=Up_Auto_Profiler_Perf
@.FileName,@.RunStopTime,@.MaxSize,@.DurationFilter,@.TraceID output
SELECT * FROM ::fn_trace_getfilterinfo(@.TraceID)
-- select * from ::fn_trace_getinfo(1)
-- select * from ::fn_trace_getinfo(default)
select * from ::fn_trace_getinfo(@.TraceID)
if (@.rc <> 0 )
RAISERROR ('Create/Run of Trace FAILED', 16, 1, @.DBID, @.DBNAME)
GO
****code****
"David Gugick" wrote:
> Can you repost your code so I can see if there are any differences?
> --
> David Gugick
>|||Hello everyone,
I appear to be having the same problem as Wanderer. I would appreciate any
suggestions.
I have a running trace. I stop the trace with
sp_trace_setstatus 1,0
I apply a filter on dbid as follows:
declare @.bigintfilter bigint
select @.bigintfilter = 22
exec sp_trace_setfilter 1,4,0,3,@.bigintfilter
I then check to see if the filter is in place:
select * from ::fn_trace_getfilterinfo(1)
The filter is not listed.
Where am I going wrong?
Thanks in advance. Any help would be appreciated.
Jim|||Check the return code from sp_trace_setfilter! All trace calls you make you
need to check the return code and check it before continuing.
Besides that which trace column is of type bigint?
GertD@.SQLDev.Net
Please reply only to the newsgroups.
This posting is provided "AS IS" with no warranties, and confers no rights.
You assume all risk for your use.
Copyright © SQLDev.Net 1991-2005 All rights reserved.
"Jim Strange" <Jim Strange@.discussions.microsoft.com> wrote in message
news:278E6A2B-FDBE-4198-95F5-8E91C2376338@.microsoft.com...
> Hello everyone,
> I appear to be having the same problem as Wanderer. I would appreciate
> any
> suggestions.
> I have a running trace. I stop the trace with
> sp_trace_setstatus 1,0
> I apply a filter on dbid as follows:
> declare @.bigintfilter bigint
> select @.bigintfilter = 22
> exec sp_trace_setfilter 1,4,0,3,@.bigintfilter
> I then check to see if the filter is in place:
> select * from ::fn_trace_getfilterinfo(1)
> The filter is not listed.
> Where am I going wrong?
> Thanks in advance. Any help would be appreciated.
> Jim
>