April 15, 2009 at 11:15 am
I have created a scripted trace for 2005 from profiler. Running it interactively, the trace seems to filter properly. When scripted and run on the server, the filter doesn't do it's job and the trace fills up in a second or two since I've limited the result size. I see the filter in the script. There are no warnings when the script is run. Thanks for any help you can provide.
I need to filter on a login name to check for activity.
Here is the script:
/****************************************************/
/* Created by: SQL Server Profiler 2005 */
/* Date: 04/15/2009 09:30:09 AM */
/****************************************************/
-- Create a Queue
declare @rc int
declare @TraceID int
declare @maxfilesize bigint
set @maxfilesize = 5
-- Please replace the text InsertFileNameHere, with an appropriate
-- filename prefixed by a path, e.g., c:\MyFolder\MyTrace. The .trc extension
-- will be appended to the filename automatically. If you are writing from
-- remote server to local drive, please use UNC path and make sure server has
-- write access to your network share
exec @rc = sp_trace_create @TraceID output, 0, N'D:\trace\IVR_User_Trace.trc', @maxfilesize, NULL
if (@rc != 0) goto error
-- Client side File and Table cannot be scripted
-- Set the events
declare @on bit
set @on = 1
exec sp_trace_setevent @TraceID, 14, 7, @on
exec sp_trace_setevent @TraceID, 14, 1, @on
exec sp_trace_setevent @TraceID, 14, 9, @on
exec sp_trace_setevent @TraceID, 14, 41, @on
exec sp_trace_setevent @TraceID, 14, 6, @on
exec sp_trace_setevent @TraceID, 14, 10, @on
exec sp_trace_setevent @TraceID, 14, 14, @on
exec sp_trace_setevent @TraceID, 14, 3, @on
exec sp_trace_setevent @TraceID, 14, 11, @on
exec sp_trace_setevent @TraceID, 14, 35, @on
exec sp_trace_setevent @TraceID, 14, 12, @on
exec sp_trace_setevent @TraceID, 15, 7, @on
exec sp_trace_setevent @TraceID, 15, 15, @on
exec sp_trace_setevent @TraceID, 15, 16, @on
exec sp_trace_setevent @TraceID, 15, 9, @on
exec sp_trace_setevent @TraceID, 15, 13, @on
exec sp_trace_setevent @TraceID, 15, 17, @on
exec sp_trace_setevent @TraceID, 15, 41, @on
exec sp_trace_setevent @TraceID, 15, 6, @on
exec sp_trace_setevent @TraceID, 15, 10, @on
exec sp_trace_setevent @TraceID, 15, 14, @on
exec sp_trace_setevent @TraceID, 15, 18, @on
exec sp_trace_setevent @TraceID, 15, 3, @on
exec sp_trace_setevent @TraceID, 15, 11, @on
exec sp_trace_setevent @TraceID, 15, 35, @on
exec sp_trace_setevent @TraceID, 15, 12, @on
exec sp_trace_setevent @TraceID, 17, 7, @on
exec sp_trace_setevent @TraceID, 17, 12, @on
exec sp_trace_setevent @TraceID, 17, 1, @on
exec sp_trace_setevent @TraceID, 17, 9, @on
exec sp_trace_setevent @TraceID, 17, 41, @on
exec sp_trace_setevent @TraceID, 17, 2, @on
exec sp_trace_setevent @TraceID, 17, 6, @on
exec sp_trace_setevent @TraceID, 17, 10, @on
exec sp_trace_setevent @TraceID, 17, 14, @on
exec sp_trace_setevent @TraceID, 17, 3, @on
exec sp_trace_setevent @TraceID, 17, 11, @on
exec sp_trace_setevent @TraceID, 17, 35, @on
-- Set the Filters
declare @intfilter int
declare @bigintfilter bigint
exec sp_trace_setfilter @TraceID, 10, 0, 7, N'SQL Server Profiler - ea8802e4-ca1f-4789-b478-f4538e6fd302'
exec sp_trace_setfilter @TraceID, 11, 1, 6, N'IVR_User'
exec sp_trace_setfilter @TraceID, 11, 0, 1, N''
-- 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:
go
April 17, 2009 at 6:24 am
I think it is because the second filter is an OR.
exec sp_trace_setfilter @TraceID, 11, 1, 6, N'IVR_User'
So it is saying you want any which do not match N'SQL Profiler...' OR the username is like IVR_User AND the username is not like ''
Changing it to:
exec sp_trace_setfilter @TraceID, 11, 0, 6, N'IVR_User'
should get the trace as you want it.
It looks like a bug to me, I setup a trace filter with an App Name and two usernames and profiled profiler setting up the trace and it used three AND's - then when I exported the definition, one of them had turned into an OR.
ed
April 17, 2009 at 8:01 am
I'd just get rid of the filter that filters out Profiler. That is just there so that the Profiler GUI doesn't trace it's own activity. That will eliminate the OR problem.
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
April 17, 2009 at 2:59 pm
I have gone ahead and raised a bug on connect about this:
https://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=433382
April 30, 2009 at 1:58 pm
edwardelliott (4/17/2009)
https://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=433382%5B/quote%5D
Nice! Thanks Edward! 😀
April 30, 2009 at 6:54 pm
What about this line?
exec sp_trace_setfilter @TraceID, 11, 0, 1, N''
A column NOT LIKE '' using an AND operator will return more records.
---------------------
Alex Rosa
http://www.keep-learning.com/blog
May 13, 2009 at 2:22 am
Hi,
I just had an alert from that connect item, Microsoft have confirmed it is a bug but won't fix it in this version as it isn't important enough. They say it might be fixed in the next version so for now you will have to manually edit your trace definition after exporting it.
Ed
May 28, 2009 at 10:57 am
edwardelliott (5/13/2009)
Hi,I just had an alert from that connect item, Microsoft have confirmed it is a bug but won't fix it in this version as it isn't important enough. They say it might be fixed in the next version so for now you will have to manually edit your trace definition after exporting it.
Ed
Thanks for these posts. When I first saw the trace file size growing, I had no idea where in the script to look to find the problem.
Once I took a look at the AND/OR parameters, I found that (for some reason) my Database ID filter had the db ID of interest but also had a 1 (OR condition) with db ID 0. I had taken out the db ID 0 clause because I didn't want it and wasn't sure how it got there.
But the 1 (OR) flag was set in the line with the db I did want to trace and was probably the cause of the large trace file sizes. In other words, it looks like the trace was asking "look for 'UPDATE%table%' OR database ID = 12" instead of "look for 'UPDATE%table%' AND database ID = 12". Changing the 1 to a 0 fixed the issue.
Thanks again,
webrunner
-------------------
A SQL query walks into a bar and sees two tables. He walks up to them and asks, "Can I join you?"
Ref.: http://tkyte.blogspot.com/2009/02/sql-joke.html
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply