Get to the system_health file target a little easier

A lot of us read information out of the built-in system_health XEvents session (or other sessions that store their files in SQL Server's ERRORLOG folder). I see a lot of code out there (including much of my own) that goes through these types of gymnastics to obtain the path:

DECLARE @Path nvarchar(260) = CONVERT(nvarchar(260), SERVERPROPERTY(N'ErrorLogFileName'));
 
SET @Path = SUBSTRING(@Path, 1, CHARINDEX(N'\ERRORLOG', @Path) - 1);
 
SELECT ...
FROM sys.fn_xe_file_target_read_file(@ErrorLogPath + N'\system_health*.xel', null, null, null);

One issue with this code is that it relies on backslashes in the path; this won't work on Linux, because the path has forward slashes (/var/opt/mssql/log/errorlog).

It may also not work on case sensitive instances or binary collations, but I didn't test that for this post.

More importantly, I recently stumbled onto the fact that these gymnastics aren't necessary. While this isn't covered in the documentation for sys.fn_xe_file_target_read_file, the function will default to searching in the ERRORLOG folder if no path is provided.

So, the following code works on both Windows and Linux, and doesn't require any variables or parsing of paths up front (well, as long as you haven't dropped the file target and created it elsewhere):

SELECT ...
FROM sys.fn_xe_file_target_read_file(N'system_health*.xel', null, null, null);

Look ma, no gymnastics!

This technique will also work for any other XEvents sessions in the ERRORLOG folder, both built-in and custom.

Aaron Bertrand

I am a passionate technologist with industry experience dating back to Classic ASP and SQL Server 6.5. I am a long-time Microsoft MVP, speak frequently at conferences, and write at SQLPerformance and MSSQLTips. In real life I am a father of two, an architect at Wayfair, and my pronouns are he/him.

2 Responses

  1. Well now, that's awesome. Thanks for posting this.

  1. June 2nd, 2020

    […] thanks to Aaron Bertrand, this query will work just […]