View SQL Server Error Logs

In this SQL Server tutorial, we will discuss how to view SQL Server view error logs. Additionally, we will cover the following topics.

View SQL Server error logs
View SQL Server error log permissions
View SQL Server agent error log
SQL Server error log file location
How to locate SQL Server error log file location
How to view SQL Server error log files
See the SQL Server error log for more information
SQL Server view error log stored procedure

Table of Contents

View SQL Server error log permissions

Before we discuss how to view SQL Server Error Log, a user needs to have some permissions. When we talk about permissions related to error logs, there are 2 options in SQL Server.

First is when we are accessing an online SQL Server instance, and send is when we have an offline SQL Server instance.

In the case of an online SQL Server instance, a user needs to be a member of the securityadmin fixed server role. On the other hand, in the case of an offline SQL Server instance, a user needs to have read permission for these 2 things.

Root\Microsoft\SqlServer\ComputerManagement10 WMI namespace
The directory containing the log files, for example, “Program Files\Microsoft SQL Server\MSSQL.n\MSSQL\LOG”

Read How to export data from SQL Server to Excel

In SQL Server, error logs help to verify whether the processes are completed successfully or not. Moreover, these logs also help us to locate any issues in the process.

Now we can easily view or display SQL Server error log by using SQL Server Management Studio or any text editor.

Now, to view the SQL Server Error Logs in SQL Server Management Studio, we can follow the following steps.

First, run SQL Server Management Studio and connect to the required database instance.
Next, from the Object Explorer, expand the Database instance.
After expanding the instance, look for Management Directory and expand it.
Under Management directory, look for SQL Server Logs directory and then right-click it and select View > SQL Server Log option.

This will open a Log File Viewer that will display all the list of error logs

Read SQL Server bulk insert from CSV file

View SQL Server Agent error log

In SQL Server, we generally use SQL Server Management Studio to view the error logs related to any component. Moreover, we specifically use Log File Viewer in the SQL Server Management Studio to display the error logs.

Now, here are the few steps that we can use to view SQL Server Agent error logs.

First, run SQL Server Management Studio and connect to the required database instance.
Next, from the Object Explorer, first, expand the Database instance and then expand SQL Server Agent directory.
Under SQL Server Agent, expand the Error Logs directory and all the error will be listed under this diectory.
Now, to view any SQL Server Agent error log, then right-click the log and click on View Agent Log.

This action will open a Log File Viewer where all the error logs related to SQL Server were listed. Moreover, we can easily view the log message and its various details.

Read SQL Server function return table

How to view SQL Server error log files

In this section, we will understand how to view SQL Server error log files. Moreover, we will also understand how to locate these error log files.

SQL Server error log file location

As discussed earlier, there are 2 ways in SQL Server to view the SQL Server Error Logs. The first way is to use the Log File Viewer in SQL Server Management Studio. And the second way is to use any text editor.

Now, if we want to view the SQL Server error logs in a text editor then, we need to know the location of these log files. So, in SQL Server, “Program Files\Microsoft SQL Server\MSSQL.n\MSSQL\LOG\ERRORLOG” is the default location of error log files.

Read View in SQL Server

How to locate SQL Server error log file location

In the previous section, we have seen the default location for the SQL Server log files. Now, in this section, we will discuss how to find the location of any SQL Server Error Log.

In SQL Server, there are many methods to implement this task, let’s, discuss these methods in detail.

Using SQL Server Configuration Manager

Here are some of the steps that we can follow to locate the SQL Server Error Log files using SQL Server Configuration Manager.

In SQL Server Configuration Manager, under SQL Server Services, right-click SQL Server Service and click on Properties.
On the Properties page, open the Startup Parameters page and in this multiple paths are given.
Now, the location for the Error logs is given in the one of the Startup Parameters values.

How to locate SQL Server error log file location — Locate SQL Server error log file location

Using SERVERPROPERTY function

As an alternative, we can also use the SERVERPROPERTY function in SQL Server to get the location of the Error Log. The SERVERPROPERTY in SQL Server is a system function that returns information related to various properties of a SQL Server instance.

Hence, we can easily use this function to get the location of the SQL Server Error Log. Moreover, we can execute the following SQL query for this task.

SELECT SERVERPROPERTY('ErrorLogFileName') 
 AS 'SQL Server Error log file location'

Now, the above will not only return the file name but also the complete path location of that error log file.

Locate SQL Server error log file location

Read Comparison Operators in SQL Server

SQL Server view error log stored procedure

Till now, we have seen how to view the SQL Server Error Log using SQL Server Management Studio. But, in SQL Server, there is also a system stored procedure that returns a list of error logs.

Moreover, we can also use it to view some specific error logs. The name of this stored procedure is SP_READERRORLOG. Let’s understand how to use this store procedure using some examples.

EXECUTE SP_READERRORLOG

First, executing this stored procedure without any parameter will return the list of all error logs. Here is the sample output of the query.

List SQL Server Error Logs using stored procedure

Now, we can also use this store procedure to view a specific error. And for this, we have to use the following syntax.

EXECUTE SP_READERRORLOG <LogNumber>, <LogType>,
                        <SearchTerm1>, <SearchTerm2>

The parameters for using this stored procedure are as follows.

LogNumber: it is an integer type parameter used to specify the log number, for example, 0 returns the current log.
LogType: it is also an integer type parameter used to specify the log type. For example, value 1 represents SQL Server error logs and 2 represents SQL Server Agent error logs.
SearchTerm1: it is an NVARCHAR type parameter used to specify the text to search from the Text column.
SearchTerm2: it is also an NVARCHAR type parameter used to specify the second text to search from the Text column.

Now, let’s use this syntax to execute an example in SQL Server. And the query for the example is as follows.

EXECUTE SP_READERRORLOG 0, 1, 'ERROR'

The above query will return the list SQL Server Error Log containing Error text in the Text column. And the result of the query is as follows.

SQL Server view error log stored procedure

Read How to see view definition in SQL Server

See the SQL Server error log for more information

Now, if you are working with SQL Server then, you often encounter this alert stating “See the SQL Server error log for more information“. This is because error logs are useful to locate any issues in the process.

Moreover, these logs also help to verify whether the processes are completed successfully or not.

Now, in this tutorial, we have already illustrated multiple methods to view and locate any error log in SQL Server.