Finding the Error Log Folder and Other Paths From T-SQL

The error log folder lives on the database server, so ask the server where it is. Guessing the install path is how you end up browsing the wrong disk for an hour.

The folder that was not there

A junior DBA once told me, “I pasted the path into File Explorer and the folder does not exist.” The path was right. The folder was on the server, and the explorer window was on a laptop.

SQL Server runs on one computer. Your query window may run on another. A path returned by T-SQL always means a path on the server’s disk. So never trust the default install location. Ask the engine instead.

Ask for the error log path

SERVERPROPERTY gives the full file name of the current error log. I cut off the file name to get the folder. The pattern looks for the last separator, either a backslash or a forward slash.

DECLARE @File nvarchar(4000) = CONVERT(nvarchar(4000), SERVERPROPERTY('ErrorLogFileName'));
DECLARE @Slash int = PATINDEX(N'%[\/]%', REVERSE(@File));

SELECT @File AS ErrorLogFile,
       CASE WHEN @Slash > 0 THEN LEFT(@File, LEN(@File) - @Slash + 1) END AS ErrorLogFolder;

The first column is the file, which is named ERRORLOG. The second is the folder, including the final separator. If the property comes back NULL, the folder stays NULL, so nothing breaks. The answer is a location only. It does not give you permission to open it.

The other folders you keep needing

The same function answers three more questions. Where do new databases put their data files, their log files, and where do backups go by default?

SELECT CONVERT(nvarchar(4000), SERVERPROPERTY('InstanceDefaultDataPath')) AS DefaultDataFolder,
       CONVERT(nvarchar(4000), SERVERPROPERTY('InstanceDefaultLogPath')) AS DefaultLogFolder,
       CONVERT(nvarchar(4000), SERVERPROPERTY('InstanceDefaultBackupPath')) AS DefaultBackupFolder;

On my test server the data and log folders are the same. On a well planned server they are on different drives. If yours match, new databases will share one drive, and that is worth a conversation.

Two more places you can ask: the files of master, and the default trace.

SELECT name, physical_name
FROM sys.master_files
WHERE database_id = DB_ID(N'master')
ORDER BY file_id;

SELECT id, path, is_default
FROM sys.traces
WHERE is_default = 1;

The first query lists the data and log files of master. The second shows the default trace file, which sits in the log folder on my server. If the default trace is switched off, the second query returns no row.

Startup arguments and service paths

The startup arguments show the exact paths the engine was told to use for master and for the error log. The service list shows what Windows starts. A service file name is a command line. It can include quotes and arguments, so do not treat it as a folder.

SELECT value_name, CONVERT(nvarchar(4000), value_data) AS StartupValue
FROM sys.dm_server_registry
WHERE value_name LIKE N'SQLArg%'
ORDER BY registry_key, value_name;

SELECT servicename, filename
FROM sys.dm_server_services
ORDER BY servicename;

In the first grid, one argument starts with -e. That is the error log path, and it matches the first result. Arguments starting with -d and -l point to the master data and log files. The second grid lists the engine service, and others such as the Agent, each with a quoted program path.

Are you on the same computer?

One last query settles the laptop question. Compare your own computer name with the server’s.

SELECT HOST_NAME() AS ClientComputer,
       CONVERT(nvarchar(128), SERVERPROPERTY('MachineName')) AS ServerComputer;

On my test box both are the same, because I ran the demo locally. On yours, a difference means the paths above are not on your machine. The dynamic views need a server-level view permission, so ask your admin if they return an error.

When you need the log text itself, use the log viewer in SSMS. For a file copy, ask for the approved route to the server. If the paths move, run these queries again after the service restarts.

Ask the server first, and the right folder is one query away.

A path from T-SQL is not a folder on your laptop, it is a location on the server.

Published by Pinal Dave on SQLAuthority. More of my work at pinaldave.com.