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 (
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.
Well now, that's awesome. Thanks for posting this.