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.
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 (
EXEC [master].[dbo].xp_readerrorlog 0
,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', '')
SET @dir = LEFT(@dir, LEN(@dir) - 1)
DROP TABLE #TmpErrorLog
So here’s a quick out of the box solution for . . . you guessed it, dynamically pulling all text files from a folder into a SQL Server table. On it’s own this script isn’t very powerful but it can be paired with SQL queries to make some powerful functionality.
To use simply change the @path variable to the directory path you’re targeting and as it states in the comments . . .