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 queries below (first one works on SQL Server 2008, second one works on 2016) use 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.
SQL Server 2008
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 AS ErrorFileLocation DROP TABLE #TmpErrorLog
SQL Server 2016
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'; -- 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 AS ErrorFileLocation DROP TABLE #TmpErrorLog