Learn more about SQL Server tools



solving sql server problems for millions of dbas and developers since 2006 join MSSQLTips for free SQL Server tips













































   Got a SQL tip?
            We want to know!

Identify location of the SQL Server Error Log file

MSSQLTips author Ashish Kumar Mehta By:   |   Read Comments (3)   |   Related Tips: 1 | 2 | 3 | More > 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


Last Update: 10/6/2011


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.

View all my tips


print tip Print  
Become a paid author





join MSSQLTips for free SQL Server tips     



Learn more about SQL Server tools
Post a comment or let the author know this tip helped you.

       All comments are reviewed, so stay on subject or we may delete your comment.

*Name   *Email Notify for updates



       Note: your email address is not published. Required fields are marked with an asterisk (*)


Get free SQL tips:

*Enter Code refresh code     



Monday, October 31, 2011 - 4:44:58 PM - pbuddy08 Read The Tip

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

 

SELECT SERVERPROPERTY('ErrorLogFileName')


Thursday, October 06, 2011 - 1:45:25 PM - Papy Normand Read The Tip

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 06, 2011 - 11:50:10 AM - Sal Young Read The Tip

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

(Get-SQLServer "ServerName").ErrorLogPath



 
Sponsor Information







Copyright (c) 2006-2014 Edgewood Solutions, LLC All rights reserved
privacy | disclaimer | copyright | advertise | about
authors | contribute | feedback | giveaways | free t-shirt | user groups | community | events | first timer?
Some names and products listed are the registered trademarks of their respective owners.