1

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.

  • When is the registry value not accurate? – Aaron Bertrand Mar 24 '16 at 11:04
  • @AaronBertrand good question - I was under the impression this could be inaccurate under certain circumstances, but now I am wondering if I am wrong on this one. – Mark Mar 24 '16 at 11:13
5
SELECT SERVERPROPERTY('ErrorLogFileName')

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

Joe Sack has a post on it

  • Sybase has select @@errorlog that gives error log file location .. just FYI. – Kin Shah Mar 24 '16 at 15:54
1

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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.