Post reply

Difficulty with SP_TRACE_SETFILTER

  • January 3, 2005 at 3:31 am

    #112927

    Hi all,

    First off - Happy New Year - may 2005 be Blessed, Peaceful and Prosperous for you.

    To my problem, then.  I have extended the stored procedure to try and report on the creation of the traces inside, as opposed to external, in case the traceid was getting "lost" somehow.

    **********start 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.
@DurationFilterValueBIGINT, --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 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 sp_trace_create @TraceId output,@Option,@TraceFile,@MaxSize,@StopTime
select @ReturnCode=@@Error
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

exec sp_trace_setstatus @TraceId,1
select @ReturnCode=@@Error
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

exec sp_trace_setfilter @TraceId,@ColumnId,@LogicalOperator,@ComparisonOperator,@Value
select @ReturnCode=@@Error
Print 'Filter 1 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)
set @columnId = 13 --Duration
set @ComparisonOperator = 4 --Greater than or equal to
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)
-- 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

ErrorHandler:
Return @ReturnCode

    **********end code*************

    I invoke the SP with the following code:

     

    **********start code*************

    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 = 2 -- 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

    --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)

    if (@rc <> 0 )

     RAISERROR ('Create/Run of Trace FAILED', 16, 1, @DBID, @DBNAME)

    GO

    **********end code*************

    I've played around with the profiler front end, and checked to see the diffence between the way that they create the trace, and the way that I did - the commented out code with the declare's etc. at the end of the SP was taken straight out of the scripting of a profiler trace.

    I've tried to apply the values as constants, rather than variables, although that largely defeats the idea of having this stored procedure receiving a duration filter.

    To no avail. I get NO message back saying that the execution of the SP_TRACE_SETFILTER failed, RC is 0, yet the filter is not found via the ::fn_trace_getfilterinfo ... while a ::fn_trace_getinfo with the same @traceid variable successfully returns the correct info, proving that the trace was reated, and that the traceid value is correct (and the fn_trace_getinfo is done after the _getfilterinfo) so there is no "resetting of @traceid" that could cause it to get 0 rows.

    It plainly looks like the sp_trace_setfilter executes but does nothing!

    So far Technet Forums, MS searches, Google searches, have been unfruitful.

    Help!!

  • January 3, 2005 at 4:01 am

    #535585

    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).

Viewing 2 posts - 1 through 1 (of 1 total)

You must be logged in to reply to this topic. Login to reply