Re: Problem using SP_TRACE_SETFILTER - it doesn't seem to apply th

Tech-Archive recommends: Fix windows errors by optimizing your registry

From: Wanderer (Wanderer_at_discussions.microsoft.com)
Date: 12/30/04 

Date: Thu, 30 Dec 2004 02:15:02 -0800

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

Relevant Pages