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.