Find location of SQL Server Error Log File

We are going to discuss 5 methods to find location of SQL Server Error Log File. We will expose different ways for a DBA to identify SQL Server Error log location. The Error log file named “ERRORLOG” can be valuable to track some issue for SQL Server. Contains user-defined events and certain system events you can use for troubleshooting.

In this tip we will take a look at 5 different ways you identify which SQL Server Error Log file is used by an instance of SQL Server.

Using tsql command (SERVERPROPERTY)
Run xp_readerrorlog.
Using SQL Server Configuration Manager
Application Event Viewer
Search on registry key

5 ways to find SQL Server Error Log File – ERRORLOG

Identify Sql Server Error Log file using tsql command

Execute SERVERPROPERTY function as below:

SELECT SERVERPROPERTY(‘ErrorLogFileName’)

AS ‘Error log file location’

You will get the results:

Identify Sql Server Error Log file using tsql command

By default, we have 6 Server Error Logs kept.

Run xp_readerrorlog.

USE MASTER

EXEC xp_readerrorlog 0, 1, N’Logging SQL Server messages in file’

Run xp_readerrorlog

Also you can run xp_readerrorlog and find the line says “Logging SQL Server messages”.

Read also other SQL Server Helping Posts:

Finding Sql Server Error Log file using SQL Server Configuration Manager

Search on Windows for – SQL Server Configuration Manager

SQL Server Configuration Manager

In SQL Server Configuration Manager, click SQL Server Services on the left side and then right-click on SQL Server (MSSQLSEVER)and select Properties from the drop down as shown below. For a named instance, right-click on the appropriate SQL Server (INSTANCENAME)
In SQL Server (MSSQLSERVER)Properties window click on the Startup Parameters. We need to find startup parameter starting with -e.

Finding Sql Server Error Log file using SQL Server Configuration Manager

Locate Sql Server Log file location Using Application Event Viewer

On windows search for “Event Viewer”
In Server Manager, expand Diagnostics, expand Event Viewer, expand Windows Logs and then select Application on the left side panel. In the right panel you need to filter for events with Event ID 17111 as shown in the below snippet. To set a filter right-click on Application and select Filter Current Log.
Expand Windows Logs and then select Application on the left side panel. In the right panel you need to filter for events with Event ID 17111.
Double click an event and you can see the event properties as shown below.

Locate Sql Server Log file location Using Application Event Viewer

Find location of SQL Server Error Log File Searching on registry key

The ERRORLOG is one of startup parameters and its values are stored in registry key that you can find like below:

Go to regedit by searching on windows
Go to path – HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server and expand your sql server version
SQLArg1 shows parameter starting with -e parameters which point to Errorlog file.