How to: File Target use in Extended Events

I have been doing a lot of posts of late about extended events but have failed to share some of the essential building blocks. I am not looking to do a series of definitions, but just share a couple of quick tips and examples here and there that will make the use of extended events a little easier to undertake.

The first one to tackle is the use of the file target. Using a file target is not difficult from a creation point of view (event session creation). But, some basics are critical or you could end up with fewer follicles.

Let’s take a quick peek at some of these tips.

File System Prep

This first little tip comes from a painful experience. It is common sense to only try and create files in a directory that exists, but sometimes that directory has to be different on different systems. Then comes a little copy and paste of the last code used that worked. You think you are golden but forgot that one little tweak for the directory to be used. Oops.

Take this example session. When you run this code, you will not see an error (don’t change anything).

However, if you try to start the session, you will receive a pretty little error. Here is the code to try and start the session, along with it’s associated error.

Msg 25602, Level 17, State 22, Line 25
The target, “5B2DA06D-898A-43C8-9309-39BBBE93EBBD.package0.event_file”, encountered a configuration error during initialization. Object cannot be added to the event session. The operating system returned error 3: ‘The system cannot find the path specified.
‘ while creating the file ‘C:\Database\XEs\FileTargetDemo_0_130670484928250000.xel’.

If you double check in the SSMS GUI, you will see that the session is there. It is just stopped. You can also check that the session exists if you recall my query from a previous post where I show the status of the session.

If you take it a step further and try to read the log file that should be created, you will be greeted once more with a reminder that there is a problem. Try this (sans change again).

And you will receive the following message:

Msg 25718, Level 16, State 3, Line 31
The log file name “C:\Database\XEs\FileTargetDemo*.xel” is invalid. Verify that the file exists and that the SQL Server service account has access to it.

The beauty here is that all is not lost. These are easy problems to get around and resolve. All it takes is to adjust the file path to be the correct path or a path that exists (to which you have permissions).

Read the Log File

If you have been reading this article, then this might seem like a bit of redundancy. That is by design. DBAs love redundancy—well at least when it means there is a means to recovery due to the redundancy.

Unlike reading from the ring_buffer, when you read from the file target, you will use different code. The file target is stored in a binary representation on disk that a function will produce as XML format for human readability. So to read a file target, you would need to do something like the following.

And you definitely do not want to try the following with a file target. That is unless of course you are looking to go bald at an earlier than expected age in this profession.

If you find yourself doing this version, all you will get for results is a short xml string similar to the following:

This is not what you would be looking for when trying to read the event session data. That said, it at least provides some information that could be of use. You will see that buffers are tracked and the file name is tracked. For this last demo, I corrected the FileTargetDemo session to use a directory on the file system that existed and to which I had the appropriate level of permissions.

I just covered two quick tips to help make life a little bit easier when dealing with file targets and extended event sessions. I will try to continue little tips like this over the coming months. I hope you have found this useful and that you will indeed be able to put it to use.

 

For more uses of Extended Events, I recommend my series of articles designed to help you learn XE little by little.

Interested in an article demonstrating the power of XE over Profiler? Check this one out!

This has been the third article in the 2018 “12 Days of Christmas” series. For a full listing of the articles, visit this page.

2 Comments - Leave a comment
  1. […] Jason Brimhall has some tips to help you use the file target in Extended Events: […]

  2. […] How To: File Target use in Extended Events – 27 December 2018 […]

Leave a comment

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.



Calendar
December 2018
M T W T F S S
« Nov   Jan »
 12
3456789
10111213141516
17181920212223
24252627282930
31  

Welcome , today is Thursday, January 17, 2019