{"id":1083,"date":"2020-01-23T06:30:56","date_gmt":"2020-01-23T14:30:56","guid":{"rendered":"http:\/\/3.209.169.194\/blogs\/erin\/?p=1083"},"modified":"2020-01-23T07:08:29","modified_gmt":"2020-01-23T15:08:29","slug":"what-events-are-in-a-trace","status":"publish","type":"post","link":"https:\/\/www.sqlskills.com\/blogs\/erin\/what-events-are-in-a-trace\/","title":{"rendered":"What Events are in a Trace?"},"content":{"rendered":"

Yes…I’m writing a post about SQL Trace<\/a>…specifically events in a trace. There are still lots of folks that use Profiler<\/a> and run server-side traces, and when I work with those customers I like to understand how the traces are configured. I want to see what events are in a trace, but I also want to see the filters and if the traces are writing to a file or rowset provider. I wrote this query ages ago and went looking for it today, which means I really should write a post so it’s easier to find \ud83d\ude42<\/p>\n

Where to start<\/h2>\n

In order to determine the events in the trace, you first need to know the ID for the trace, which is automatically generated when you create the trace.\u00a0 You can find the ID in sys.traces:<\/p>\n

SELECT *
\nFROM sys.traces;
\nGO<\/p>\n

If I run this on my local instance, I get the output below:<\/p>\n

\"sys.traces<\/a>
sys.traces output, part I<\/figcaption><\/figure>\n
\"sys.traces<\/a>
sys.traces output, part II<\/figcaption><\/figure>\n

<\/h2>\n

What to review<\/h2>\n

I broke the output into two images because there are a few things to point out.\u00a0 The trace with id = 1 is, almost always, represents the default trace.\u00a0 I yet to find a system where it is not the default trace, but it’s possible (e.g. someone disables the default trace, or manages to get another trace to start up before the default trace).\u00a0 If it’s writing to the LOG folder where SQL Server is installed, then I’m confident it’s the default trace and I’m not worried about it.<\/p>\n

The second trace, with id = 2, is one I created as a server-side trace. Notice that for the trace with id = 3, the path is NULL.\u00a0 This indicates that the trace is not writing to a file, but to a rowset provider (this is why is_rowset = 1 for that row).\u00a0 The rowset provider is typically, but not always, the Profiler UI.\u00a0 When I see this for a client, I ask if they use a third-party monitoring tool (as this is how traces from third-party tools can appear).\u00a0 Even if the customer has a monitoring tool, I want to see what events are being collected, and the filter(s).<\/p>\n

Some other items of interest in the output:<\/p>\n