Re: Problem using SP_TRACE_SETFILTER - it doesn't seem to apply th
From: Olu Adedeji (i-oluade_at_microsoft.com)
Date: 12/30/04
- Next message: Wanderer: "Re: Problem using SP_TRACE_SETFILTER - it doesn't seem to apply th"
- Previous message: IT Dep: "Re: SBS 2003 Premium SQL 2000 XP_sendmail"
- In reply to: Wanderer: "RE: Problem using SP_TRACE_SETFILTER - it doesn't seem to apply th"
- Next in thread: Wanderer: "Re: Problem using SP_TRACE_SETFILTER - it doesn't seem to apply th"
- Reply: Wanderer: "Re: Problem using SP_TRACE_SETFILTER - it doesn't seem to apply th"
- Messages sorted by: [ date ] [ thread ]
Date: Thu, 30 Dec 2004 09:53:59 -0000
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...
> > >
- Next message: Wanderer: "Re: Problem using SP_TRACE_SETFILTER - it doesn't seem to apply th"
- Previous message: IT Dep: "Re: SBS 2003 Premium SQL 2000 XP_sendmail"
- In reply to: Wanderer: "RE: Problem using SP_TRACE_SETFILTER - it doesn't seem to apply th"
- Next in thread: Wanderer: "Re: Problem using SP_TRACE_SETFILTER - it doesn't seem to apply th"
- Reply: Wanderer: "Re: Problem using SP_TRACE_SETFILTER - it doesn't seem to apply th"
- Messages sorted by: [ date ] [ thread ]
Relevant Pages
|
|
