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
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.
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.
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.
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.
Next Steps
- Keep this tip handy to find out where the SQL Server Error Log file is located
- Refer to these other related tips:
