How to Access the SQL Server Error Log

Problem

In SQL Server there are two primary sets of error logs. One for the database engine and a second for SQL Server Agent. Reviewing these logs is routine for Database Administrators and sometimes Developers when troubleshooting issues. What are the different ways to view these error logs? Are there different scenarios when you would use one view other another? Do any other error logs exist that SQL Server Professionals should review?

Solution

This article is in the category of “simple things I do every day.” Those who support Microsoft SQL Server should be aware of the available options for reviewing the SQL Server and SQL Agent error logs. We will explore scenarios when you may need to use these various reviewing methods.

Option 1 – SSMS Log File Viewer

One option to view the SQL error logs using SQL Server Management Studio (SSMS) is in the Object Explorer. This is a common and easy way to look at SQL error logs when using SSMS and connected to a SQL Server. One advantage is that you can easily look at multiple logs together and apply search filters across multiple error logs. In the SSMS top menu, click View and then Object Explorer.

Expand the Management folder and then expand the SQL Server Logs folder.

 Next, double-click on the Current log or any of the Archive logs.

The SQL error log will open in a new Log File Viewer window.

When to Use

• Actively working in SSMS.
• Need to review multiple logs.
• Need to easily apply filters across multiple logs.
• Need an easy way to export the logs to a file for further analysis.
• Prefer a GUI interface.

Note: Sometimes, large error logs may be slow to open in the Log File Viewer. Options 2 and 3 will show faster ways to view the log file.

SQL Agent Error Log from SSMS

Likewise, you can view the SQL Agent Error log via SSMS. From Object Explorer, expand SQL Server Agent, then expand Error Logs. Below is the list of the current and archived SQL Agent logs.

Double-click one of the error logs to open it in a new window with Log File Viewer.

From here, you can view the SQL Agent error logs. In the left pane, select multiple logs by clicking the checkboxes beside the logs; then, you can apply filters to narrow down your search or view of the error log entries.

View and Filter Across Multiple Logs

Below is an image of the Log File Viewer, where you can select multiple log files by clicking the check boxes next to the desired logs. Here, you can choose from multiple log types, including SQL Server, SQL Server Agent, Database Mail, and Windows Event logs. The Log File Viewer allows you to apply a filter across all selected logs and see the combined results. This is good when trying to troubleshoot an error that happened in a given time window.

Option 2 – System Store Procedure

You can also view SQL error logs from an SSMS query window by calling the system stored procedure sp_readerrorlog, which takes four optional parameters:

When to Use:

• Actively in SSMS in a query window.
• Need a fast, easy way with fewer clicks.
• Require only errors from the SQL Server log or SQL Agent Log.
• Need to write custom code capturing specific info from the error log.

T-SQL Code Examples

Below are three examples of calls to the stored procedure sp_readerrorlog:

 
--1) No parameters - returns the current SQL Server Error log
exec sp_readerrorlog;  
 
--2) Show SQL Agent Log number 2 entries that contain the word 'Agent'
exec sp_readerrorlog 2, 2, 'Agent';
 
--3) Show entries in the current SQL Server log that include the words 'start' and 'master'
exec sp_readerrorlog 0, 1, 'start', 'master';
 

Example 2 results:

Example 3 results:

Option 3 – Text Editor

Notepad or other text editors can be used to open the SQL and SQL Agent log files directly.

When to Use:

• If troubleshooting why SQL Server or SQL Agent fails to start.
• Require a quicker view of error logs. When opening large error logs, a text editor is faster than the SSMS Log File Viewer.
• Need to save copies of the SQL error logs to review at a later time.

The location of these files is determined at the time SQL is installed and is typically stored at C:\Program Files\Microsoft SQL Server\MSSQLXX.MSSQLSERVER\MSSQL\Log\, where XX denotes the SQL Server version number installed.

If you can’t find the error logs, you can run the SQL Server Configuration Manager. Then, go to the SQL Server service, right-click, and select Properties. Select the Startup Parameters tab. There, you can see the path where the error logs are located.

In Windows File Explorer, navigate to the Error log folder and review the Error Logs with a Text Editor.

SQL Server Error Logs

The current log in the SQL Server Error Logs is named ERRORLOG and has no extension. The previous logs are named ERRORLOG.#, denoting the previous logs in sequence.

SQL Agent Error Logs

The current log is named SQLAGENT.OUT, while the other logs are named SQLAGENT.#.

Open the Log Files in NotePad Editor

To open the ERRORLOGs or SQLAGENT logs, right-click on the log and select Open with. Select Notepad or your preferred text editor.

Wrap Up

This tip showed three ways to view the SQL Server and SQL Agent error logs. These methods may prove useful when troubleshooting SQL in different situations. Additionally, I introduced a method for filtering and viewing errors across multiple types of error logs.

Next Steps

Review other MSSQLTips.com articles to learn more tips about managing SQL error logs and other features of SQL Server.

• Increase the Number of SQL Server Error Logs
• Identify location of the SQL Server Error Log file
• Simple way to find errors in SQL Server error log
• Search multiple SQL Server Error Logs at the same time