Setting up filtering for server-side tracing

Question

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 Trace script in my case since I ended up selecting only the Locks / Deadlock Graph event - and that event doesn't expose a Database Name as an event column

• if I pick other events, like RPC completed that do expose the Database Name as an event column, I can filter on the Database Name and that filter is also properly added to the Create Trace script.

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!

Answer 1

Does that mean the server-side trace is always only checking the database it's been creating in?

No, the server-side trace has the scope of just that: the server. When I create (what I think is) the exact trace definition and export it, I get the following snippet:

-- Set the Filters
declare @intfilter int
declare @bigintfilter bigint

set @bigintfilter = 1000000
exec sp_trace_setfilter @TraceID, 13, 0, 4, @bigintfilter

exec sp_trace_setfilter @TraceID, 35, 0, 6, N'AdventureWorks'
-- Set the trace status to start
exec sp_trace_setstatus @TraceID, 1

I am using SQL Server Profiler 2008 R2 as well. If you do not filter on the database name, then you will be capturing trace events for all of the instance databases. As to why your export didn't seem to include that portion of the filter, I can't say for sure. I know this goes without saying, but I'd check your trace definition within Profiler before you export to ensure the database name is in fact included as a filter (again, I know you've probably double and tripple checked this, but I had to say it).

Answer 2

I'm not sure what's going on with your Profiler because I was able to do this before. Sometimes the GUI to set up the filtering goes a bit haywire, and if you change certain things in the trace dialog box, all the filters are reset to defaults. All that said, I honestly don't remember if I used the 2008 R2 or 2012 management tools to generate my script.

In any event, here is the code to do the filtering you want:

exec sp_trace_setfilter @TraceID, 35, 0, 6, N'<database name 1>'
exec sp_trace_setfilter @TraceID, 35, 1, 6, N'<database name 2>'
exec sp_trace_setfilter @TraceID, 35, 1, 6, N'<database name 3>'

MSDN: sp_trace_setfilter