Getting the error log location in an efficient way

Question

I am using this sql to get my error log location:

EXEC xp_readerrorlog 0, 1, N'Logging SQL Server messages in file'

However, sometimes I have very large log file (about 1gb, and I do not have control over the cycling of these files) the query above takes about 10 seconds to run on these large files, which is too long.

Given that this piece of text is always in the first few lines of the file, it would be good if I could say "give me top 1 and then stop reading", but I don't think the xp_readerrorlog support this?

Or, alternatively, if I knew the date of the first line in the file, I could specify a date range like so:

DECLARE @startDate DATETIME
DECLARE @endDate DATETIME
SET @startDate = '2016-01-28 12:00:00'
SET @endDate = DATEADD(mi, 5, @startDate)
EXEC xp_readerrorlog 0, 1, N'Logging SQL Server messages in file', NULL, @startDate, @endDate

Which is much quicker. However, I don't know how I can work out this date - in many cases it is nearly the same as the start time of the instance process (SELECT login_time FROM sysprocesses WHERE spid = 1 but this is not guaranteed)

I have also tried using master..xp_instance_regread to read the log file location from SOFTWARE\Microsoft\MSSQLServer\MSSQLServer\Parameters, but this does not always seem to be accurate.

Does anyone have any suggestions how I could work out the log file location in an efficient way, even when my log files are large.

Answer 1

SELECT SERVERPROPERTY('ErrorLogFileName')

Actually it is an (undocumented) serverproperty, which appears to work on SQL2005+

Joe Sack has a post on it

Answer 2

Does anyone have any suggestions how I could work out the log file location in an efficient way, even when my log files are large.

Assuming you are running SQLServer 2008R2 SP1 and up, you can use sys.dm_server_registry DMV as below :

SELECT    @@servername as ServerName
        , RIGHT(cast(value_data as nvarchar(max)), LEN(cast(value_data as nvarchar(max))) - 2) as ErrorLogLocation
FROM sys.dm_server_registry
where value_name = 'SQLArg1' -- this is the error log location