Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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!

share|improve this question
1  
I asked a similar question when I was trying to filter on HostName. In that case, it was my not selecting all columns that stopped the filter making it into the scripted trace. Good odds you've encountered the same. –  Mark Storey-Smith Jun 29 '12 at 20:31
add comment

2 Answers 2

up vote 3 down vote accepted

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).

share|improve this answer
    
Seems my Profiler somehow has some "outages" - maybe it's too hot for it, or something :-) Thanks for confirming that it should work! :-) –  marc_s Jun 29 '12 at 16:03
1  
No problem! Glad to help. That's definitely odd behavior, and a great reminder to all of us that we need to do exactly as you did: Check what a GUI scripts out. –  Thomas Stringer Jun 29 '12 at 16:06
add comment

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

share|improve this answer
    
OK, good to hear - thanks! I'll try with the 2012 tools and see if that works –  marc_s Jun 29 '12 at 15:59
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.