I have a stock of scripted trace definitions that I use for different levels of diagnostics, none of which filter by HostName. I needed to filter traffic by host today so:

  1. Fired up Profiler
  2. Created an empty trace
  3. Added SP:Start/Complete
  4. Set a filter on hostname
  5. Scripted to test.sql file

Open file in SSMS, no sign of the filter. Rinse, repeat, same again. Assuming I was doing something daft I looked up the value to use (harder to find than it should be!) and added to my trace definition manually, which worked.

EXEC sp_trace_setfilter @TraceId, 8, 0, 6, N'MyHostName'

Came back to check on why I couldn't get this to work from Profiler and same result. I found mention of this being a problem when scripting traces for 2000 from SSMS but no mention of this situation with SSMS2005/2008 to 2005/2008 servers. Is this a bug in Profiler?


2 Answers 2


When the script is generated in Profiler 2005, the default value saved for the logical operator is 1='OR', so the trace will capture more information than we initially intended.

exec sp_trace_setfilter @TraceID, 8, 1, 0, N'HOSTNAME1'    -- OR 
exec sp_trace_setfilter @TraceID, 8, 1, 0, N'HOSTNAME2'
exec sp_trace_setfilter @TraceID, 8, 1, 0, N'HOSTNAME3'
set @bigintfilter = 10 -- reads >= 10
exec sp_trace_setfilter @TraceID, 16, 0, 4, @bigintfilter  -- AND
exec sp_trace_setfilter @TraceID, 35, 1, 6, N'DB'          -- OR

In Profiler >=2008, the script has 0(AND) for the first occurrence of the column being filtered.

exec sp_trace_setfilter @TraceID, 8, 0, 0, N'HOSTNAME1'    -- AND               
exec sp_trace_setfilter @TraceID, 8, 1, 0, N'HOSTNAME2'  
exec sp_trace_setfilter @TraceID, 8, 1, 0, N'HOSTNAME3' 
exec sp_trace_setfilter @TraceID, 35,0, 6, N'DB'           -- AND                
set @bigintfilter = 10 -- reads
exec sp_trace_setfilter @TraceID, 16, 0, 4, @bigintfilter  -- AND

This looks like a bug in Profiler 2005.


You need to check if the server trace file has the column added in the list of columns for all your events. So if you have events 10 and 12 (RPC:Completed and SQL:BatchCompleted), you have to run sp_trace_setevent for all columns, including for column Hostname:

exec sp_trace_setevent @TraceID, 10, 8, @on
exec sp_trace_setevent @TraceID, 12, 8, @on

where 10 and 12 - events and 8 - column Hostname

sp_trace_setevent says:

"Users must execute sp_trace_setevent for each column added for each event. During each execution, if @on is set to 1, sp_trace_setevent adds the specified event to the list of events of the trace. If @on is set to 0, sp_trace_setevent removes the specified event from the list."

After that the filter should be able to work properly.

  • The issue is that if I set HostName as a filter in Profiler, it doesn't appear in the scripted trace definition. If I add the filter manually to my scripted definition, it works fine. Aug 19, 2011 at 10:46
  • Mark - You said "Open file in SSMS, no sign of the filter.". From this I understand that the sp_trace_setfilter is not scripted. But have you checked if the event is set for that specific column: exec sp_trace_setevent _TraceID, 12, 8, _on (instead of 12 need your used event id)?
    – Marian
    Aug 19, 2011 at 11:11
  • You might be right. I just went throught the process again with a single event and it scripted just fine. I would of sworn I selected HostName with "Select column" at the top of the grid (to ensure it was selected for all events) but looks like I went wrong somewhere! I'll play around with various combinations later and update the original Q if there are still any oddities. Aug 19, 2011 at 13:12

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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