2

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.

2
  • When is the registry value not accurate? Mar 24, 2016 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, 2016 at 11:13

2 Answers 2

6
SELECT SERVERPROPERTY('ErrorLogFileName')

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

Joe Sack has a post on it

1
  • Sybase has select @@errorlog that gives error log file location .. just FYI.
    – Kin Shah
    Mar 24, 2016 at 15:54
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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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