Identify location of the SQL Server Error Log file

By:   |   Updated: 2011-10-06   |   Comments (6)   |   Related: 1 | 2 | 3 | > SQL Server Configurations


Problem

How do I find out where the SQL Server Error Log file is located for a specific SQL Server instance? In this tip we look at different ways a DBA can identify the location of the SQL Server Error Log file used by an instance of SQL Server.

Solution

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

1. Reading the SQL Server Error Logs
2. Using SQL Server Configuration Manager
3. Using Windows Application Event Viewer

Let's take a look at each of the above options in detail.

Identify SQL Server Error Log File used by SQL Server Database Engine by Reading SQL Server Error Logs

The SQL Server Error Log is a great place to find information about what is happening on your database server. You can execute the below TSQL command which uses the XP_READERRORLOG extended stored procedure to read the SQL Server Error Log to find the location of SQL Server Error Log file used by the instance of SQL Server. 

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

sql server error log file location

XP_READERRRORLOG

The parameters you can use with XP_READERRRORLOG are mentioned below for your reference:

1. Value of error log file you want to read: 0 = current, 1 = Archive #1, 2 = Archive #2, etc...
2. Log file type: 1 or NULL = error log, 2 = SQL Agent log
3. Search string 1: String one you want to search for
4. Search string 2: String two you want to search for to further refine the results
5. Search from start time
6. Search to end time
7. Sort order for results: N'asc' = ascending, N'desc' = descending

By default, there are six archived SQL Server Error Logs along with the ERRORLOG which is currently used. However, it is a Best Practice to increase the number of SQL Server Error Logs from the default value of six. Hence I recommend you read this tip Increase the Number of SQL Server Error Logs.

Identify SQL Server Error Log File used by SQL Server Database Engine Using SQL Server Configuration Manager

1. Click Start -> Programs -> Microsoft SQL Server 2008 -> Configuration Tools -> SQL Server Configuration Manager

2. 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) service.

sql sever configuration manager

3. In SQL Server (MSSQLSERVER) Properties window click on the Advanced tab and then expand the drop down next to Startup Parameters. The location of SQL Server Error Log file is mentioned next to the "-e" startup parameter as highlighted in the snippet below.

sql configuration manager startup parameters

Identify SQL Server Error Log file used by SQL Server Database Engine Using Application Event Viewer

1. Click Start -> All Programs -> Administrative Tools -> Server Manager.

2. 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.

windows event viewer

3. To view the location of SQL Server Error Log file double click an event and you can see the event properties as shown below.

windows event properties
Next Steps


sql server categories

sql server webinars

subscribe to mssqltips

sql server tutorials

sql server white papers

next tip



About the author
MSSQLTips author Ashish Kumar Mehta Ashish Kumar Mehta has been contributing to the MSSQLTips.com community since 2009 with over 60 tips.

This author pledges the content of this article is based on professional experience and not AI generated.

View all my tips


Article Last Updated: 2011-10-06

Comments For This Article




Tuesday, February 16, 2021 - 8:50:57 PM - Robert Simpson Back To Top (88249)
The example in the first block of code in the article is missing a parameter. There are only 6 parameters, where in the list of parameters below that, there are supposed to be 7. A third "NULL" is missing: xp_readerrorlog 0, 1, N'Logging SQL Server messages in file', NULL, NULL, NULL, N'asc'

Wednesday, August 16, 2017 - 7:56:53 AM - Thirupathi Back To Top (64911)

 It's really good artical.... Very helpful information 

 

Wednesday, December 10, 2014 - 5:21:05 PM - TechnoCaveman Back To Top (35562)

Thank you. Worked on SQL 2008 R2 like a charm. 
Extended information is very helpful.  

Monday, October 31, 2011 - 4:44:58 PM - pbuddy08 Back To Top (14973)

You can also run the below command in SSMS. It queries the server properties instead of the registry

 

SELECT SERVERPROPERTY('ErrorLogFileName')

Thursday, October 6, 2011 - 1:45:25 PM - Papy Normand Back To Top (14793)

Hi,

It is possible in an application to get all these informations because it is needing to use the namespace

http://msdn.microsoft.com/en-us/library/ms212724(v=SQL.100).aspx

The class to use is the Server Class http://msdn.microsoft.com/en-us/library/microsoft.sqlserver.management.smo.server(v=SQL.100).aspx

To enumerate the ErrorLogs  : http://msdn.microsoft.com/en-us/library/microsoft.sqlserver.management.smo.server.enumerrorlogs(v=SQL.100).aspx

To read the current ErrorLog : http://msdn.microsoft.com/en-us/library/ms210384(v=SQL.100).aspx with an example of code and results ( in VC# )

To read an ErrorLog ( but you need its oreder number ) : http://msdn.microsoft.com/en-us/library/ms210393(v=SQL.100).aspx

to get path of the ErrorLogs path : http://msdn.microsoft.com/en-us/library/microsoft.sqlserver.management.smo.server.errorlogpath(v=SQL.100).aspx

When you want to obtain the names or the contents of ErrorLogs with PowerShell, you are using the properties/methods of the Server Class of the Microsoft.SqlServer.Management.Smo namespace.

You may need to reference several assemblies in an application. For more explanations, i would suggest to have a look at the links i provided

There is an error in the documentation : the ReadErrorlogs() method does not return a DataTable but a StringCollection ( same error for SQL Server 2008,2008 R2,Denali )

Have a nice day

Thursday, October 6, 2011 - 11:50:10 AM - Sal Young Back To Top (14790)

You can add PowerShell to your list.  Here's the code

(Get-SQLServer "ServerName").ErrorLogPath














get free sql tips
agree to terms