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