Sql Server Error Log is very helpful to diagnose and troubleshoot problems. Error log files contain user-defined events and certain system events, which are very helpful for troubleshooting.

In this article, I will explain various alternative approaches to find the location of the Sql Server Error Log file.

Approach 1: Identify Sql Server Error Log file location using SERVERPROPERTY function

If you are able to connect to the Sql Server, then you can use the SERVERPROPERTY function as shown below to find the location of the Sql Server ERRORLOG file:

SELECT SERVERPROPERTY('ErrorLogFileName') 
               AS 'Error log file location'

RESULT:

Below image shows the Sql Server ErrorLog folder with error log files. Here ERRORLOG is the current error log file and remaining six files are the archived SQL Server Error Logs. By default sql server maintains six archived Error log files.

[ALSO READ] How to change Sql Server ErrorLog files location

Approach 2: Finding Sql Server Error Log file location using system stored procedure SP_READERRORLOG

This is one more option to find the location of the Sql Server Error Log file, if you are able to connect to the Sql Server. This system stored procedure can be used to view the content of the Error Log file. The information about the Sql Server ErrorLog file location will be present in the first couple of rows in this sp’s result as shown below

EXECUTE SP_READERRORLOG

RESULT:

If you don’t want to search the location of the error log file in the result of this SP. Then you can execute the stored procedure SP_READERRORLOG with the optional parameters values as shown below to get the Sql Server Error log file location:

EXEC SP_READERRORLOG 0, 1,N'Logging SQL Server messages in file'

RESULT:

This stored procedure takes four parameters and below is the description for each of these parameters:

Parameter 1: The log file which you want to read. Value 0 means current Error log file, 1 means the Archived Error log file ERRORLOG.1, 2 means the Archived Error log file ERRORLOG.2 and so.on.
Parameter 2: This parameter specifies whether you want to read Sql Server Error Log file or Sql Server Agent Log file. This parameter value 1/NULL means Sql Server error log file, 2 means Sql Server Agent log file.
Parameter 3:First string you want to searh in the error log
Parameter 4:Second string you want to search for to further filter the result

Approach 3: Locate Sql Server Log file location using Sql Server Configuration Manager (An option if you are not able to connect to Sql Server)

If you are not able to connect to Sql Server, then this approach will be very hand in identifying the location of the Sql Server Error Log. Go to Sql Server Configuration Manager as shown in the below image:

Alternatively, you can go to the Sql Server Configuration Manager exe location as listed in the below table and then double click on it.

In the Sql Server Configuration Manager as shown in the below image. Select the Sql Server Service and then right click the Sql Server service in the right pan and select the properties option. It will bring-up the Sql Server Service properties dialog, in the dialog go to the Startup Parameters tab. In the Startup Parameter tab, the existing parameter with prefix -e will be the location of the Sql Server Error log (i.e. the path appearing after the prefix parameter -e).

Approach 4: Locate Sql Server ErrorLog file loation using XP_READERRRORLOG extended stored procedure

If you are able to connect to Sql Server, then you can use the extended stored procedure XP_READERRRORLOG to find the location of the Sql Server ErrorLog file location as shown below. By the way the system stored procedure SP_READERRORLOG internally calls this extended stored procedure only.

xp_readerrorlog 0, 1, N'Logging SQL Server messages in file', 
                        NULL, NULL, NULL, N'asc' 

RESULT:

This extended stored procedure takes seven parameters and below is the description for each of these parameters:

Parameter 1: The log file which you want to read. Value 0 means current Error log file, 1 means the Archived Error log file ERRORLOG.1, 2 means the Archived Error log file ERRORLOG.2 and so.on.
Parameter 2: This parameter specifies whether you want to read Sql Server Error Log file or Sql Server Agent Log file. This parameter value 1/NULL means Sql Server error log file, 2 means Sql Server Agent log file.
Parameter 3:First string you want to searh in the error log
Parameter 4:Second string you want to search for to further filter the result
Parameter 5: Here we can specify the time, if we specify it will search the error log entries which are captured from this time.
Parameter 6: Here if we specify the time, then it will search the error log entries which are captured till this time.
Parameter 7: This parameter specify the sort order of the result, for ascending sort order we need to pass parameter value as N’asc’ and for descending sort order we have to pass this parameter value as N’desc’.

[ALSO READ] 100 Frequently used queries in Sql Server