Learn more about SQL Server tools


Tutorials          DBA          Dev          BI          Career          Categories          Events          Whitepapers          Today's Tip          Join

Tutorials      DBA      Dev      BI      Categories      Events

DBA    Dev    BI    Categories


Identify location of the SQL Server Error Log file

By:   |   Read Comments (4)   |   Related Tips: 1 | 2 | 3 | More > SQL Server Configurations


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.


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
xp_readerrorlog 0, 1, N'Logging SQL Server messages in file', NULL, NULL, N'asc' 

sql server error log file location


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

Last Update:

About the author


Post a comment or let the author know this tip helped.

All comments are reviewed, so stay on subject or we may delete your comment. Note: your email address is not published. Required fields are marked with an asterisk (*).

*Name    *Email    Notify for updates 

Get free SQL tips:

*Enter Code refresh code     

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

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

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



Thursday, October 06, 2011 - 1:45:25 PM - Papy Normand Back To Top


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


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 06, 2011 - 11:50:10 AM - Sal Young Back To Top

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

(Get-SQLServer "ServerName").ErrorLogPath

Learn more about SQL Server tools