SP_Trace_Setfilter not working? - SQL Server Forums

Home | Weblogs | Forums

Site Sponsored By: SQLDSC - SQL Server Desired State Configuration

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Administration (2000)

SP_Trace_Setfilter not working?

Author

Topic

Wanderer
Master Smack Fu Yak Hacker

1168 Posts

Posted - 2004-12-29 : 06:26:45

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_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 = 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 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
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
if @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
	end
end

ErrorHandler:
Return @ReturnCode

The Code that calls the trace


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 = 5 -- 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
		+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 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(@TraceID)

if (@rc <> 0 )
	RAISERROR ('Create/Run of Trace FAILED', 16, 1, @DBID, @DBNAME)
GO

Ta...

*##* *##* *##* *##*

Chaos, Disorder and Panic ... my work is done here!

Wanderer
Master Smack Fu Yak Hacker

1168 Posts

Posted - 2005-01-03 : 04:52:40

Wow - 11 reads, but no responses? That's unusual for here. Let me add some more detail, in the hope of some responses.

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.


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

I've played arpund 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 wit the declare's etc. 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!

Help!!

*##* *##* *##* *##*

Chaos, Disorder and Panic ... my work is done here!

rockmoose
SQL Natt Alfen

3279 Posts

Posted - 2005-01-03 : 05:17:26

It looks like You are setting the filter after you have started the trace, which is not Ok according to BOL.
A filter can only be set on a trace with status 0 (stopped).
Also You are checking the @@error, but You should check teh returncode of sp_trace_setfilter instead.

rockmoose

Wanderer
Master Smack Fu Yak Hacker

1168 Posts

Posted - 2005-01-03 : 05:40:02

Ahah ! That looks promising... I can see what you mean regards applying the filter after the starting of the trace.

Hmm - I take it you mean I should be doing:

exec @ReturnCode=sp_trace_setfilter ....

Will check...

TIA

*##* *##* *##* *##*

Chaos, Disorder and Panic ... my work is done here!

Wanderer
Master Smack Fu Yak Hacker

1168 Posts

Posted - 2005-01-03 : 05:45:26

Definitely looks promising. I now have responses from ::fn_trace_getfilterinfo, and it looks as it should.

Funny how you can stare at something, and just not see it...

It works - at least in the first testing!! Thanks Moose !!!

*##* *##* *##* *##*

Chaos, Disorder and Panic ... my work is done here!

rockmoose
SQL Natt Alfen

3279 Posts

Posted - 2005-01-03 : 06:09:57

Well, I had plans on putting some trace code together myself (temporarily on ice).
I was thinking along the lines of putting the events, columns, returncodes etc into metadata tables.
Then create the traces "dynamically" depending what is selected from these tables.
(predefined traces could also be stored in tables, and perhaps wrap things up in sp:s,
eg spStartTrace 'SpExecs', spStartTrace 'Logins', that sort of thing)

I found that setting up the traces in code was a bit tedious and with tendencies to become "hard coded".
But the trace stuff is pretty cool/interesting

rockmoose

Wanderer
Master Smack Fu Yak Hacker

1168 Posts

Posted - 2005-01-04 : 02:15:34

Yeah, it is pretty hardcoded atm.

You definitely could have a metadata driven approach, as there is clearly a M:N relationship between events and objects, with a resolution table.

I've also written a fairly rough and ready Import Procedure in case you are interested...

*##* *##* *##* *##*

Chaos, Disorder and Panic ... my work is done here!

rockmoose
SQL Natt Alfen

3279 Posts

Posted - 2005-01-04 : 12:58:48

Yes, that would be interesting, perhaps I should dust off the old plans and put something together.
What put me off somewhat was the possible differences between SQL2k and Yukon as to how they handle tracing.
I have not investigated the matter any further though.

rockmoose

Wanderer
Master Smack Fu Yak Hacker

1168 Posts

Posted - 2005-01-05 : 06:46:07

Well, for what it's worth, here you go:


if exists (select 'SP exists' from sysobjects where name = 'UP_AutoImport_TraceFiles' and xtype = 'p')
	drop proc UP_AutoImport_TraceFiles
go
create procedure UP_AutoImport_TraceFiles
	@TraceFilePath varchar(255)
--	Create By:	Regan Galbraith
--	Create On:	2004-12-28
--	Purpose:	
--		This stored procedure was written to facilitate the automatic import of profiler trace files.
--		
--	Example:
--		exec UP_AutoImport_TraceFiles 'c:\Auto_Prof\'
--
--	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 - creation and adding of comment
--
--
as
begin
set nocount on
set quoted_identifier off

create table #TraceFileList(TraceFileName varchar(128))

create table #DirExists
(FileExists int,
FileIsDir int,
ParentDirExists int)

declare @TraceFileName varchar(255)
declare @dirSQL varchar(255)
declare @ErrorDir varchar(6)

insert into #DirExists exec master..xp_fileexist @TraceFilePath
if not exists(select 'Dir Exists' from #DirExists where fileExists = 0 and FileIsDir = 1 and ParentDirExists = 1)
	begin
	set @ErrorDir = 'Data'
	goto DirError
end

set @dirSQL = 'dir '+@TraceFilePath+'*.trc /b'
--print @dirSQL

delete from #TraceFileList
insert into #TraceFileList
exec master..xp_cmdshell @dirSQL
--select * from #TraceFileList

declare TraceFileList cursor for select TraceFileName from #TraceFileList

open TraceFileList
fetch next from TraceFileList into @TraceFileName

while @@fetch_status <> -1
begin
-- this assumes a naming standard where your back names are databasename_ and then more detail. If you have 
-- a naming standard for backups of simply databasename.bak, then change this to charindex ('.' , for example.
select @TraceFileName = @TraceFilePath+@TraceFileName
select 'Files Processed : ' +@TraceFileName
if @TraceFileName is not null
	begin
	INSERT INTO [ProfilerLog].[dbo].[sqltext_combined]
		([EventClass], [DatabaseID], [TextData], [SPID], [Duration], [EndTime], 
		[Reads], [Writes], [CPU], [TransactionID], [NTUserName], [NTDomainName], 
		[HostName], [ClientProcessID], [ApplicationName], [LoginName], [StartTime], 
		[Permissions], [Severity], [EventSubClass], [ObjectID], [Success], [IndexID], 
		[IntegerData], [ServerName], [ObjectType], [NestLevel], [State], [Error], [Mode], 
		[Handle], [ObjectName], [DatabaseName], [FileName], [OwnerName], [RoleName], 
		[TargetUserName], [DBUserName], [TargetLoginName], [ColumnPermissions])
	SELECT [EventClass],[DatabaseID],Cast([TextData] as varchar(6000)) as TextData,
		[SPID],[Duration],[EndTime],[Reads],[Writes],[CPU], [TransactionID], [NTUserName], [NTDomainName],
		[HostName], [ClientProcessID], [ApplicationName], [LoginName], [StartTime], 
		[Permissions], [Severity], [EventSubClass], [ObjectID], [Success], [IndexID], 
		[IntegerData], [ServerName], [ObjectType], [NestLevel], [State], [Error], [Mode], 
		[Handle], [ObjectName], [DatabaseName], [FileName], [OwnerName], [RoleName], 
		[TargetUserName], [DBUserName], [TargetLoginName], [ColumnPermissions]
		
	FROM ::fn_trace_gettable(@TraceFileName, default)
--	where max(DATALENGTH(textdata)) <= 6000
end	
    fetch next from TraceFileList into @TraceFileName
end

close TraceFileList
deallocate TraceFileList
goto finish

DirError:
Print 'An error with the '+@errordir+' Directory given - please check that it exists. Thanks'
drop table #TraceFileList
drop table #DirExists
RETURN -1

Finish:
drop table #TraceFileList
drop table #DirExists
RETURN 0

end

--		exec UP_AutoImport_TraceFiles 'c:\Auto_Prof\'

*##* *##* *##* *##*

Chaos, Disorder and Panic ... my work is done here!

Wanderer
Master Smack Fu Yak Hacker

1168 Posts

Posted - 2005-01-05 : 06:47:51

Oh, and of course, the table structure:


IF EXISTS (SELECT * FROM SYSOBJECTS WHERE NAME = 'sqltext_combined' AND XTYPE = 'u')
	DROP TABLE sqltext_combined
GO
CREATE TABLE [sqltext_combined] (
 	[RowNumber] [int] IDENTITY (1, 1) NOT NULL ,
	[DateEntered] [datetime] not null default getdate(),
 	[EventClass] [int] NULL ,
	[DatabaseID] [int] null ,
 	[TextData] [varchar] (6000) ,
 	[SPID] [int] NULL ,
 	[Duration] [bigint] NULL ,
 	[EndTime] [datetime] NULL ,
	[Reads] [bigint] NULL ,
	[Writes] [bigint] NULL ,
	[CPU] [int] NULL ,
	[TransactionID] [Bigint] null, 
	[NTUserName] [varchar] (128) null, 
	[NTDomainName] [varchar] (128) null, 
	[HostName] [varchar] (128) null, 
	[ClientProcessID] [int] null,
	[ApplicationName] [varchar] (128) null, 
	[LoginName] [varchar] (128) null, 
	[StartTime] [datetime] null,
	[Permissions] [integer] null, 
	[Severity] [integer] null, 
	[EventSubClass] [integer] null, 
	[ObjectID] [integer] null, 
	[Success] [integer] null, 
	[IndexID] [integer] null, 
	[IntegerData] [integer] null, 
	[ServerName] [varchar] (128) null, 
	[ObjectType] [integer] null, 
	[NestLevel] [integer] null, 
	[State] [integer] null, 
	[Error] [integer] null, 
	[Mode] [integer] null, 
	[Handle] [integer] null, 
	[ObjectName] [varchar] (128) null, 
	[DatabaseName] [varchar] (128) null, 
	[FileName] [varchar] (128) null, 
	[OwnerName] [varchar] (128) null, 
	[RoleName] [varchar] (128) null, 
	[TargetUserName] [varchar] (128) null, 
	[DBUserName] [varchar] (128) null, 
	[TargetLoginName][varchar] (128) null, 
	[ColumnPermissions] [integer] null
 	 PRIMARY KEY  CLUSTERED 
 	(
 		[RowNumber]
 	)  ON [PRIMARY] 
 ) ON [PRIMARY]
GO
-- 
-- 
create index SQLTEXT_COMB_IX1
on SQLTEXT_COMBINED
(rownumber)
go
create index SQLTEXT_COMB_IX2
on SQLTEXT_COMBINED
(spid,duration)
go
create index SQLTEXT_COMB_IX3
on SQLTEXT_COMBINED
(spid,cpu)
go
create index SQLTEXT_COMB_IX4
on SQLTEXT_COMBINED
(spid,reads)
go
create index SQLTEXT_COMB_IX5
on SQLTEXT_COMBINED
(spid,reads)
go
--create index SQLTEXT_COMB_IX6
--on SQLTEXT_COMBINED
--(textdata)
-- go

*##* *##* *##* *##*

Chaos, Disorder and Panic ... my work is done here!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources