Scripting SQL Server Error Log Location in TSQL Queries

While it's possible to view the SQL Server error log location in the SSMS GUI, sometimes you want to build a script that'll write a log to the same directory as the error log; we don't want to have to modify our script for every server it runs on.

Enter the ServerProperty function.  There is at least one undocumented server property 'errorLogFilename' that will give us the full path to the SQL ERRORLOG.  This is nice, as we can simply run this to return that:

SELECT SERVERPROPERTY('ErrorLogFileName');

Now let's say we want, instead of the error log, simply the path to the error log directory, where we'll write our own log.  We'll just pull out the error log filename (with a fun use of the reverse function):

declare @errorlog varchar (200)
select @errorlog=convert (varchar(200),SERVERPROPERTY('ErrorLogFileName'))

SELECT REVERSE(
 Substring(
  REVERSE(@errorlog)
  , CHARINDEX('\', REVERSE(@errorlog)) + 1
  , len(@errorlog)
 )
)

It's not strictly speaking necessary to set a variable to do the above, but it sure makes the query cleaner.

That series of REVERSE calls, by the way, is simply a way to strip out everything after (and including) the last backslash.  I've tried to format it here so it makes a little more sense than having it all on one line.  Here's how it works:

We reverse @errorlog within the substring so that it is backwards, and then we find the first occurrence of the character '\'.  That's where we start our substring, and we don't truncate it on the end.  Since we reversed it within the substring, we select the REVERSE of the substring, which gives us our original string.

If you want to keep the trailing \ on the string, you simply can remove the ' + 1 ' at the end of the CHARINDEX line.