Finding the SQL Server error log folder

sql-server
Page content

The SQL Server error log folder is increasingly used to store other files such as the System Health monitor’s XEL event files as well as the default location for Extended Event traces.
I’ve seen some really creative approaches to getting this information over the years, so I thought I’d share my slightly more boring version which works as far back as SQL Server 2008:

/*
Script to get the error log directory (pre-SQL Server 2012)
*/
declare @logpath nvarchar(1024)

select @logpath = convert(nvarchar(1024),serverproperty('errorlogfilename'))
select @logpath = left(@logpath, (len(@logpath) - charindex('\\',reverse(@logpath))))
select @logpath as errorlogfolder

There’s not much to it because it’s using the ErrorLogFilename property of the SERVERPROPERTY function to do most of the groundwork.
The select could have been put into one line, but for the sake of (some sort of) legibility I thought I’d split it into three lines.

If your server is SQL Server 2012 or above, it gets even easier to find the location of the error log folder:

/*
Script to get the error log directory (post-SQL Server 2012)
*/

select \[path\] from sys.dm\_os\_server\_diagnostics\_log\_configurations

As an aside, it’s quite easy to modify the name of the errorlog to e.g. add a .log or .txt exension to it which makes it a lot easier to open externally. In case you were wondering, yes, the technique documented in that post can also be used to change the default error log folder as well.

I hope you find it as useful as I have (especially for extracting stuff out of the System Health Monitor!). :)

A simple way to get the default SQL Server data and log path