It seems very wrong to write a post that talks about Trace, after all I’ve done to advocate that everyone start using Extended Events (XE). However, I know there are a lot of you who still use Trace because you’re running SQL Server 2008R2 and earlier, so you all get a free pass. Until you upgrade. If you’re running SQL Server 2012 or higher, I strongly recommend that you use XE. But that’s the not the main point of this post. What I really want to do is step through filtering a .trc or .xel file to remove selected events.
Trace
Now, if you’ve worked with Trace for a long time, you may be wondering why you would ever filter events because, let’s be honest, you might not know you can do that (I didn’t for ages). You can! The Profiler UI isn’t where you typically perform data analysis, but one reason you might filter out events is if you’re using Distributed Replay and you need to remove events to avoid generating errors during the reply. To do this, open the .trc file in the Profiler UI, then select File | Properties… Within the Trace File Properties window, go to the Events Selection tab, then select Column Filters… Within the Edit Filter window, you can chose a column (or multiple columns) on which to filter your data. If I want to remove all events before a specific time, I would edit the EndTime:
This removes all events before 11:31PM on November 12, 2015. After you have removed the events, you can save the remaining data as a new .trc file through File | Save As | Trace File…
Extended Events
Filtering events from an Extended Events file is even easier. Open the .xel file within Management Studio, then select Extended Events | Filters (you can also select the Filters icon in the Extended Events toolbar). Within the Filters window, you can choose to filter on time, and/or any of the other fields captured for the events:
Once you select Apply, all events before 11:31PM will be removed, as well as those with less than 1000 logical_reads. The remaining events can again be saved to a new file (Extended Events | Export to | XEL File…), or you can just run analysis against the filtered data. You can remove the filter at any time by going back to the Filters window and selecting Clear All.
Summary
Hopefully this helps if you ever need to remove events from a .trc or .xel file. Note that I always save the filtered data as a new file – I prefer to keep the original just in case I need all the events for some reason.