Extended Events has a number of options for the storage of the events. These include Ring_buffer, pair_matching, event_file and several others. As expected, each of these have different options for configuration. This post is about the event_file target. As a side note, Extended Event sessions will have only one target, it is possible to have more than one. The System_Health session does have two, event_file and ring_buffer.
Below you will find a screenshot of the options for the event_file target. These are the same type of options we have had in Profiler for many years.
File name just identifies what the file name will start with. As you can see in the image below, SQL Server adds a large number to the file name. This number represents the number as defined below.
“The integer value is calculated as the number of milliseconds between January 1, 1601 and the date and time the file is created.”
The other options are pretty straight forward. The Maximum file size has two option, MB and GB.
If I only have a few sessions, I could easily go and look at each session. However, what if I have a large number of sessions and I want to review these settings for all sessions. It would be nice to be able to pull this information out of the database using T-SQL.
When using Extended Events, there are a number of DMVs that can help us gather information about the sessions that exist on the server. If you do a Google search on “DMV Extended Events” you will get a number of links that point to one of the views in the list below. Notice that they all start with “dm_ex_”. These are very useful views, however the data we are looking for can not be found in any of these.
To get this information we need to look in a different set of views. These views start with “server_event” for an on-prem instance and “database_event” for an Azure SQL database. Below is a list of this set of views.
To get the information we are looking for we need to use two of these, server_event_session_fields and server_event_sessions. We will need to join these two views on the event_session_id column in both views. If you look at the image below, you will notice that SQL Server stores the values as a row in the view rather than a column for each option. Even though the GUI offers us two options for the maximum file size, MB and GB, the actual value is stored in MB.
For the maximum file size and the maximum rollover files, you will not see them in these views unless they are changed from the default. If you look at the image below there is one of the options missing, maximum rollover files. This is because it is set to the default value of 5.
Just a little note about the max_rollover_files option. This has a default of 5, as stated earlier, however if you see that it has a value of 0, then the “Enable File Rollover” is set to false. As in the image below. If you are wondering why all the options are not active, this is because the target has already been created.
To get the defaults we need to run the query below.
SELECT name
, object_name
, type_name
, column_value
, description
FROM sys.dm_xe_object_columns
WHERE object_name = ‘event_file’
When you run the above query, your result set will look like what is below. Notice the Max File Size and Max Number of files.
To get the data we are looking for we need to use the query below.
SELECT
s.name,
f.name,
f.value
FROM
sys.server_event_session_fields AS f
INNER JOIN
sys.server_event_sessions AS s
ON
f.event_session_id = s.event_session_id
WHERE f.name IN (‘filename’ ,‘max_file_size’,‘max_rollover_files’
, ‘filename’)
ORDER BY s.name
I have taken above code and used the PIVOT to hopefully make the results more readable. This code also takes into account the default values, that as you may remember is in a different location.
Here is the complete code for an on-prem instance of SQL Server, including the PIVOT.
DECLARE @query AS NVARCHAR(MAX);
( OptionName sql_variant,
Filename sql_variant,
MaxFileSize sql_variant,
MaxRolloverFiles sql_variant)
SET @cols = STUFF((SELECT DISTINCT ‘,’ + QUOTENAME(
name)
FROM sys.server_event_session_fields
WHERE name IN (‘filename’ ,‘max_file_size’,‘max_rollover_files’
, ‘filename’)
FOR XML PATH(”), TYPE
).value(‘.’, ‘NVARCHAR(MAX)’)
,1,1,”)
SET @query = ‘Insert #EEoptions SELECT SessionName AS ”OptionName”, ‘ + @cols + ‘ from
(
SELECT
s.name AS ”SessionName”,
f.name as ”OptionName2”,
f.value
FROM
sys.server_event_session_fields AS f
INNER JOIN
sys.server_event_sessions AS s
ON
f.event_session_id= s.event_session_id
WHERE f.name IN (”filename” ,”max_file_size”,”max_rollover_files”
, ”filename”)
pivot
(
Max(value)
FOR OptionName2 IN (‘ + @cols + ‘)
) p ‘
EXECUTE(@query)
, Filename
, ISNULL(MaxFileSize, 1024) AS ‘MaxFileSize’
, ISNULL(MaxRolloverFiles, 5) AS ‘MaxRolloverFiles’
FROM #EEOptions
