How does one consistently find the correct path to the Extended Event Log file (XEL file)?
This is a topic that I ventured into some time ago. The previous article can be read here. In that article I covered some of the various trouble spots with capturing the file path for various XE log files. One of the main problems being that there is frequently an inconsistency in where XE logs may actually be stored.
Using what was shown in that previous article, I have some improvements and minor tweaks to fill some gaps I hadn’t completed in the previous script.
If you are still not using Extended Events, I recommend checking out this library of articles that will cover just about all of the basics concerning Extended Events.
New and Improved
First, lets just dive straight into the new script.
DECLARE @ErrorPath VARCHAR(256) = CONVERT(VARCHAR(256), SERVERPROPERTY('ErrorLogFileName'));
DECLARE
@SessionName VARCHAR(128) = NULL --'AlwaysOn_health'
, @csessionname VARCHAR(128);
CREATE TABLE #SessionPaths
(
SessionName VARCHAR(128)
, XEFilePathVARCHAR(256)
, SelectPhase VARCHAR(24)
);
DECLARE xessions CURSOR LOCAL STATIC FORWARD_ONLY READ_ONLY FOR
SELECT
ses.name
FROM sys.server_event_sessions ses
WHERE ses.name = ISNULL(@SessionName, ses.name)
ORDER BY ses.name;
OPEN xessions;
FETCH NEXT FROM xessions
INTO
@csessionname;
WHILE @@FETCH_STATUS = 0
BEGIN
IF EXISTS
(
SELECT
1
FROM sys.server_event_sessions ses
INNER JOIN sys.server_event_session_fields sesf
ON ses.event_session_id = sesf.event_session_id
WHERE sesf.name = 'filename'
AND ses.name = @csessionname
)
BEGIN
IF
(
SELECT
CHARINDEX('', CONVERT(VARCHAR(256), sesf.value))
FROM sys.server_event_sessions ses
INNER JOIN sys.server_event_session_fields sesf
ON ses.event_session_id = sesf.event_session_id
WHERE sesf.name = 'filename'
AND ses.name = @csessionname
) > 0
BEGIN
INSERT INTO #SessionPaths
(
SessionName
, XEFilePath
, SelectPhase
)
SELECT
ses.name AS SessionName
, LEFT(CONVERT(VARCHAR(256), sesf.value), LEN(CONVERT(VARCHAR(256), sesf.value))
- CHARINDEX(
''
, REVERSE(CONVERT(
VARCHAR(256)
, sesf.value
)
)
) + 1) AS XEFilePath
, 'Phase1'
FROM sys.server_event_sessions ses
INNER JOIN sys.server_event_session_fields sesf
ON ses.event_session_id = sesf.event_session_id
WHERE sesf.name = 'filename'
AND ses.name = @csessionname;
END;
ELSE
BEGIN
INSERT INTO #SessionPaths
(
SessionName
, XEFilePath
, SelectPhase
)
SELECTDISTINCT
cte1.SessionName
, CASE
WHEN RIGHT(LEFT(cte1.targetvalue, LEN(cte1.targetvalue)
- CHARINDEX('', REVERSE(cte1.targetvalue)) + 1), 1) = ''
THEN LEFT(cte1.targetvalue, LEN(cte1.targetvalue)
- CHARINDEX('', REVERSE(cte1.targetvalue)) + 1)
ELSE LEFT(@ErrorPath, LEN(@ErrorPath) - CHARINDEX('', REVERSE(@ErrorPath)) + 1)
END AS XEFilePath
, CASE
WHEN RIGHT(LEFT(t2.file_name, LEN(t2.file_name)
- CHARINDEX('', REVERSE(t2.file_name)) + 1), 1) = ''
THEN 'Phase2'
ELSE 'FailSafe'
END
FROM
(
SELECT
REPLACE(CONVERT(NVARCHAR(128), sesf.value), '.xel', '*.xel') AS targetvalue
, ses.nameAS SessionName
FROM sys.server_event_sessions ses
INNER JOIN sys.server_event_session_fields sesf
ON ses.event_session_id = sesf.event_session_id
WHERE sesf.name = 'filename'
AND ses.name = @csessionname
)cte1
OUTER APPLY sys.fn_xe_file_target_read_file(cte1.targetvalue, NULL, NULL, NULL) t2;
END;
END;
FETCH NEXT FROM xessions
INTO
@csessionname;
END;
CLOSE xessions;
DEALLOCATE xessions;
SELECT
*
FROM #SessionPaths sp;
DROP TABLE #SessionPaths;One of the things I wanted to accomplish with this update was to find the correct path for all of the sessions on the server. As mentioned in the previous article, sometimes there are complications with that. Due to the way log files can be specified for an XE session, behaviors can be a bit funky sometimes when trying to parse the correct paths. Due to those problems, I couldn’t quite short-cut the logic in the previous script and had to do the less desirable thing and create a cursor.
In addition to the cursor, I threw in a fix for when a full path is not declared for the session (at the time of creation) and the session was subsequently never started. In these odd cases, the script had been returning an empty result set and thus was not working properly. Now, it is fixed and here is an example of the output.
The third column in this result set is purely for informational purposes so I could determine at which point the file path was being derived. For the 30+ sessions running on my test instance, most paths are resolved via the first select. In the image, that is denoted by the label “Phase1” and circled in red. The system_health session happened to be running, but did not have a full path declared so it fell into the “Phase2” resolution group and is circled in blue. The last group includes those cases where a path could not be resolved for any number of reasons so they fall to the “FailSafe” grouping and an example is circled in green in the image.
Why
Truth be told, there is a method to short cut this script and get the results faster but I felt it would be less accurate. I could obviously just default to the “FailSafe” group automatically if a full path is not defined in the session creation. Would that be accurate though? Most of the time it would be accurate, but then there are the edge cases where occasionally we forget that something has changed. One such case of this is if after the session is created, you decide the SQL Server log files needs to be moved from the default path (this is where the XEL files default to if no path is defined)?
I have run across multiple scenarios where the logs were required (both technical as well as political) to be moved from the default location. Ideally, this move occurs prior to server startup. When the log file path is changed, the logs are not moved automatically to the new location. This, for me, is a case where it is best to be thorough rather than snake bit. I also like to document these things so I can compare them later if necessary.
Alternatively, here is the just good enough to pass muster version of that script.
/* or the "good enough" version */DECLARE @ErrorPath VARCHAR(256) = CONVERT(VARCHAR(256), SERVERPROPERTY('ErrorLogFileName'));
SELECTDISTINCT
cte1.SessionName
, CASE
WHEN RIGHT(LEFT(cte1.targetvalue, LEN(cte1.targetvalue) - CHARINDEX('', REVERSE(cte1.targetvalue)) + 1), 1) = ''
THEN LEFT(cte1.targetvalue, LEN(cte1.targetvalue) - CHARINDEX('', REVERSE(cte1.targetvalue)) + 1)
ELSE LEFT(@ErrorPath, LEN(@ErrorPath) - CHARINDEX('', REVERSE(@ErrorPath)) + 1)
END AS XEFilePath
, CASE
WHEN RIGHT(LEFT(cte1.targetvalue, LEN(cte1.targetvalue) - CHARINDEX('', REVERSE(cte1.targetvalue)) + 1), 1) = ''
THEN 'Quick'
ELSE 'FailSafe'
END
FROM
(
SELECT
REPLACE(CONVERT(NVARCHAR(128), sesf.value), '.xel', '*.xel') AS targetvalue
, ses.nameAS SessionName
FROM sys.server_event_sessions ses
INNER JOIN sys.server_event_session_fields sesf
ON ses.event_session_id = sesf.event_session_id
WHERE sesf.name = 'filename'
) cte1;
Conclusion
In the article today, I have shown some of the internals to retrieving file paths for Extended Event Sessions. I dove into metadata to pull out the path for the session and discussed some concerns for some of these methods. In the end, you have a few viable options to help retrieve the file path in a more consistent fashion.
To read more about Extended Events, I recommend this series of articles.
