Get to the system_health file target a little easier
May 28th, 20202
Get to the system_health file target a little easier
May 28th, 20202

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);
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):

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.

By: 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, write at Simple Talk, SQLPerformance, and MSSQLTips, and have had the honor of speaking at more conferences than I can remember. In non-tech life, I am a husband, a father of two, a huge hockey and football fan, and my pronouns are he/him.

2 Responses

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

  1. June 2, 2020

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