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 2 (of 2 total)

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

Cookies on SQLServerCentral

This website stores cookies on your computer.

These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.

To find out more about the cookies we use, see our Privacy Policy