Can you query SQL Server's Error Log file location or size?

Why yes.  You can!  Working a new server today that I have limited access to... and I needed a way to get around that limitation.  I just needed to find the location of the SQL Server error log files -- and you know what else?  The size, too, because it was taking an ungodly amount of time to open the error log within SSMS.  The server hasn't been cycled in months, so I was sure the thing was pretty big.  

What does the DBA without sysadmin privileges do?  😏 

 

DECLARE @logfiles TABLE (

       [FilArchive#] TINYINT,

       [Date] DATETIME,

       [LogFileSizeB] BIGINT

)

 

INSERT @logfiles

EXEC xp_enumerrorlogs

 

SELECT

       [FilArchive#],

       [Date],

       CONVERT(VARCHAR(50),CAST(SUM(CAST([LogFileSizeB] AS FLOAT)) / 1024 / 1024 AS DECIMAL(10,4))) + ' MB' SizeMB

FROM

       @logfiles

GROUP BY

       [FilArchive#],

       [Date],

       [LogFileSizeB];

 

-- to identify error log file location

SELECT SERVERPROPERTY('ErrorLogFileName') [Error_Log_Location];

The current one is not quite as large as I expected, but here you go -- the location and size of the current and all archived SQL Server Error logs.

Enjoy!