January 3, 2005 at 3:31 am
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. @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. -- -- 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
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