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, such as the use of the file target. 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.

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).

Source code

-- Create the Event Session
IF EXISTS(SELECT * 
          FROM sys.server_event_sessions 
          WHERE name='FileTargetDemo')
    DROP EVENT SESSION FileTargetDemo 
    ON SERVER;
GO
CREATE EVENT SESSION FileTargetDemo
ON SERVER
--+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
--+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
/* Pick an event for this demo */
--ADD EVENT sqlserver.buffer_node_page_life_expectancy()
--+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
--+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
ADD EVENT sqlserver.buffer_node_page_life_expectancy()
/* Note two targets exist here */
ADD TARGET package0.ring_buffer,
ADD TARGET  package0.asynchronous_file_target(
     SET filename='C:\Database\XEs\FileTargetDemo.xel',max_file_size = 5,max_rollover_files = 4)
WITH (MAX_DISPATCH_LATENCY = 5SECONDS)
GO

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.

Source code

-- Start the Event Session
ALTER EVENT SESSION FileTargetDemo 
ON SERVER 
STATE = START;
GO

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.

Source code

DECLARE @SessionName VARCHAR(64) = 'FileTargetDemo';
 
SELECT sn.SessionName
	, CASE WHEN ISNULL(es.name,'No') = 'No'
		THEN 'NO'
		ELSE 'YES'
		END AS XESessionExists
	, CASE WHEN ISNULL(xe.name,'No') = 'No'
		THEN 'NO'
		ELSE 'YES'
		END AS XESessionRunning
	FROM (SELECT @SessionName AS SessionName) sn
		LEFT OUTER JOIN sys.server_event_sessions es
			ON sn.SessionName = es.name
		LEFT OUTER JOIN sys.dm_xe_sessions xe
			ON es.name = xe.name

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).

Source code

/* read the session data */
SELECT CAST(event_data AS xml) AS TargetData
            FROM sys.fn_xe_file_target_read_file('C:\Database\XEs\FileTargetDemo*.xel',NULL,NULL, NULL)

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.

Source code

/* read the session data */
SELECT CAST(event_data AS xml) AS TargetData
            FROM sys.fn_xe_file_target_read_file('C:\Database\XEs\FileTargetDemo*.xel',NULL,NULL, NULL)

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.

Source code

/* do not do this */
 SELECT CAST(target_data AS xml) AS TargetData
            FROM sys.dm_xe_sessions AS s
            INNER JOIN sys.dm_xe_session_targets AS t
                ON s.address = t.event_session_address
            WHERE s.name = 'FileTargetDemo'
              AND t.target_name = 'event_file';

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

Source code

<EventFileTarget truncated="0">
  <Buffers logged="1" dropped="0" />
  <File name="C:\Database\XE\FileTargetDemo_0_130670493477970000.xel" />
</EventFileTarget>

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.