Finding Errorlog File Directory in SQL Server

Working with some new monitoring software, I need to locate the directory of the SQL Server’s Errorlog.  This location is not standard and differs by each version of SQL Server.  Note that pictured below is a test vitural machine, and I do not install SQL Server on the C – drive on any work related server.

To find the SQL Server error log, I would query the “SERVERPROPERTY(‘ErrorLogFileName’)“:

SELECT SERVERPROPERTY('ErrorLogFileName') 
AS 'ERRORLOG FILE LOCATION';

SERVERPROPERTY('ErrorLogFileName')

To get the directory of SQL Server’s Errorlog, I would trim off the file errorlog file name:

SELECT 
left( convert(nvarchar(500), SERVERPROPERTY('ErrorLogFileName')) , 
LEN (convert(nvarchar(500), SERVERPROPERTY('ErrorLogFileName')))- 8 ) 
as 'Errorlog Location';

Directory of SQL Server's Errorlog

To put the the directory of SQL Server’s Errorlog in something like a variable that could be used in a stored procedure:

DECLARE @Errorlog_Location nvarchar(500)

SET @Errorlog_Location = 
'Errorlog Location: ' + 
left( convert(nvarchar(500), SERVERPROPERTY('ErrorLogFileName')) , 
LEN (convert(nvarchar(500), SERVERPROPERTY('ErrorLogFileName')))- 8 )

PRINT @Errorlog_Location

Directory of SQL Server's Errorlog in something like a variable

Tags:

Leave a Reply