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
NOTE: To set the download location to a folder on an external SD you will need to have a rooted device. See the following tutorial to get an idea of how to root your device.
(1) Open firefox and type about:config into the address bar and tap Go. You will see a page displayed like below.
You may want to bookmark this for easier future access.
(2) In the search bar, type down (for download) and wait for Firefox to filter the list.
(3) Scroll down to browser.download.folderList and tap it to display the editing controls. Change the value from 1 to 2 (from “default always” to “last used folder”).
(4) Tap the large “+” above the list to add a new setting.
(5) On the right side, you see “Boolean” tap that and change to “String”.
(6) Name the new setting as browser.download.dir
(7) Below the name of the setting enter the file path for the folder location you want to use. In my instance I had created the folder Download on the external SD. For example:
(8) Once you have entered the file path tap return and then tap Create.
From this point on when you download something it should go to the folder you have specified.