I'm trying to set up a server-side trace in SQL Server 2008 R2. While I have no trouble selecting events and data columns, the filtering seems a bit odd...
I was trying to set up a filtering on both the duration of the SQL batch or stored procedure (filter anything >= 1000 ms), but I also wanted to filter on the database name involved.
When I export the trace definition from SQL Profiler to a
*.sql file, I however don't seem to find any filtering on the database name:
-- lots of other stuff up here - setting up trace events and so on... -- Set the Filters declare @intfilter int declare @bigintfilter bigint exec sp_trace_setfilter @TraceID, 10, 0, 7, N'SQL Server Profiler - 100479e4-6eeb-4608-af17-64141f1631ec' set @bigintfilter = 1000000 exec sp_trace_setfilter @TraceID, 13, 0, 4, @bigintfilter ....
So what's up with that? Why can I specify a database name to filter on - but the trace definition doesn't contain it, in the end?
- Does that mean the server-side trace is always only checking the database it's been creating in?
- But then why can I specify a filter by database name?
- Does this also mean there's no way to do cross-database server-wide tracing on e.g. deadlocks?
Update: after some more toying around, I came to the following conclusion:
the filter on the database name was not applied to the
Create Tracescript in my case since I ended up selecting only the
Locks / Deadlock Graphevent - and that event doesn't expose a
Database Nameas an event column
if I pick other events, like
RPC completedthat do expose the
Database Nameas an event column, I can filter on the
Database Nameand that filter is also properly added to the
I can't figure out how I was able to actually define a filter on
Database Name, since normally, if an event doesn't expose a column, that column is also not available in the
Column filter dialog....
Anyway - for whatever reasons that are beyond me (for now, at least),
Deadlock Graph doesn't expose the
Database Name and thus I cannot filter on that property, either....
Thanks all that helped and provided insightful comments!