Reading Extended Event File Session Data

Comments: 8 Comments
Published on: June 21, 2015

Using Extended Events to trap/trace information allows the trapping of that information to various targets. One of the targets I will frequently tell people to use is the file target. The reasoning for this is the opportunity this affords to be able to review the output  at a later time whether the event session is running or not.

Along  with that recommendation, I also show some quick code to pull the data back from the session target (the file on disk). This code is written to dynamically pull the file information out of the XE Dynamic Management Views to make it a little easier on the user (and a bit less likely to have a typo). That code looks a bit like the following:

This works really well when the session is running. It pulls the desired file path and name back from the DMVs and one is able to parse the session data. But what if that session is stopped?

Well, if the session is stopped, we have a problem. With a stopped session, the session above will not produce any results. The short of this is that SQL Server removes the entries from the sys.dm_xe_session_targets and sys.dm_xe_sessions DMVs. Due to that, there is a bit of a tweak to be able to query those files for the sessions that have been stopped.

To query those files, one could rewrite the previous query to something like this:

Now, I bet you may be asking why stop an Extended Event session from running. Afterall, extended events is extremely light weight and has little impact on the server, right? True that may be, there is still a cost for traces. If you can just run a trace for a targeted time period, then why not limit the amount of tracing?

Looking at the code, you can see that I reverted to a less dynamic approach to read those event files. Instead of trying to get that info direct from the database, I am just telling SQL Server where to find the files on disk (note the filepath that is passed).

There you have it. A means to query these trace files whether the XE trace is running or stopped.

page 1 of 1

June 2015
« May   Jul »


Welcome , today is Thursday, September 21, 2017