How to identify the location of the SQL Server Error Log file

So you want to find out where the SQL Server Error Log file is located for a specific SQL Server instance?

The Error Log is a great place to start tracking down reasons why SQL Server might be experiencing problems.

The query below uses the extended stored procedure XP_READERRORLOG, which is typically used to read error logs directly, to return the location of the SQL Server Error Log files. The returned value is then cleaned up so only the directory location is provided. You can then copy and paste the result into your file explorer. 

USE Master;
GO

DECLARE @dir AS VARCHAR(MAX)

-- Command will create the temporary table in tempdb
CREATE TABLE [dbo].[#TmpErrorLog] (
	[LogDate] DATETIME NULL
	,[ProcessInfo] VARCHAR(20) NULL
	,[Text] VARCHAR(MAX) NULL
	);

-- Command will insert the errorlog data into temporary table
INSERT INTO #TmpErrorLog (
	[LogDate]
	,[ProcessInfo]
	,[Text]
	)
EXEC [master].[dbo].xp_readerrorlog 0
	,1
	,N'Logging SQL Server messages in file'
	,NULL
	,NULL
	,N'asc';

-- retrieves the data from temporary table and writes it to a variable to remove everything but directory location
SET @dir = (
		SELECT REPLACE(REPLACE(REPLACE([Text], 'Logging SQL Server messages in file ', ''), '''', ''), 'ERRORLOG', '')
		FROM #TmpErrorLog
		)
SET @dir = LEFT(@dir, LEN(@dir) - 1)

SELECT @dir

DROP TABLE #TmpErrorLog
Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s