Issue With Sp_trace_setfilter
Apr 28, 2008
Hi Folks, first time poster, long time listener.
We're SQL Server 2005 SP2 x64, and I'm playing around with server side tracing. Specifically, we're trying to audit anyone doing UPDATE on the database. We don't want to capture anything else (like user 'SELECT's), that would generate unnecessary events.
What I've done:
1) Create a trace/template in SQL profiler to capture "SQL:StmtCompleted"
2) Add a filter on 'TextData' column, using 'Like'; 'UPDATE%'
3) Profiler trace using this template works fine, I'm capturing the correct data and nothing else.
4) In the profiler tool, I did 'Export-Script Trace Definition'. This created a script file that had the following line:
exec sp_trace_setfilter @TraceID, 1, 1, 6, N'UPDATE%'
5) I can run the server side trace (I'm tracing to a filename 1.trc on the server). Unfortunately - here's the issue - when I open the trace file using profiler, it looks like I have captured all SQL:StmtCompleted events, even the ones that I did not want (like SELECT statements). The filter is not working.
Where is my error? Anyone have ideas?
Thanks in advance for the help!
Donger
View 2 Replies
Jul 24, 2007
Hello, how do you setup a Profiler filter, that has mixed AND/OR operators. Thru the Profiler GUI, I don't even see how you would ask for any statement/proc with READS over 10,000 OR DURATION over 1000ms. How do you do an "OR" thru the GUI?
Forgetting the GUI and doing traces thru scripts, the @logical_operator isn't too logical, and I can't find any description for setting this parameter. If you only have one filter, it doesn't matter if you set it to 0 (AND) or to 1 (OR)... But if you want multiple filters, so one filter is Reads >= 10,000 and the other filter is Duration >= 1000ms., how do you do that? I tried it with the @logical_operator of "1" on both, but then I also got other events with a NULL value in READS.
Code Snippet
-- @traceID = 1
-- @columnID = 13 (Duration)
-- @logicalOperator = 1 (OR)
-- @comparison_operator = 4 (greater or equal)
-- @value = 1000000 (1000 ms)
sp_trace_setfilter 1, 13, 1, 4, 1000000
-- @traceID = 1
-- @columnID = 16 (Reads)
-- @logicalOperator = 1 (OR)
-- @comparison_operator = 4 (greater or equal)
-- @value = 10000 (# Reads)
sp_trace_setfilter 1, 16, 1, 4, 10000
Looks like DURATION is never NULL for these events>>>
RPC:Completed
SP:Completed
SPtmtCompleted
SQL:BatchCompleted
SQLtmtCompleted
but the READS value cane be null (for SP:Completed at least).
Any ideas on how to mix AND/OR operators in multiple filters of one trace?
Thanks, Bruce
View 1 Replies
View Related
