How to View SQL Server Error Logs

As a developer, working with SQL Server debugging can be quite challenging. Monitoring and troubleshooting are important for maintaining a proper database environment. One of the main features is the SQL Server Error Log, which records basic things like startup and shutdown processes, errors, and warnings.

How to View SQL Server Error Logs

In this post, I will explain to you the process of accessing these logs so you can better understand and resolve any issues that arise during development.

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 second 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.

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

View SQL Server Error Logs

In SQL Server, error logs help verify whether processes are completed successfully and locate any issues.

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

To view the SQL Server Error Logs in SQL Server Management Studio, 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.

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 a 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 errors will be listed under this directory.
• 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 are listed. Moreover, we can easily view the log message and its various details.

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 two ways to view SQL Server error logs in SQL Server. The first is to use the Log File Viewer in SQL Server Management Studio, and the second is to use any text editor.

If we want to view the SQL Server error logs in a text editor, we need to know the location of these log files. In SQL Server, “Program FilesMicrosoft SQL ServerMSSQL.nMSSQLLOGERRORLOG” is the default location of error log files.

Check out How To Check DB Size In SQL Server

How to Locate SQL Server Error Log File Location

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

There are many methods to implement this task in SQL Server. Let’s discuss these methods in detail.

Using SQL Server Configuration Manager

Here are some of the steps 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 one of the Startup Parameters values.

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.

SQL Server View Error Log Stored Procedure

We have seen how to view the SQL Server Error Log using SQL Server Management Studio until now. However, 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.

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. The query for the example is as follows.

EXECUTE SP_READERRORLOG 0, 1, 'ERROR'

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

See the SQL Server Error Log for More Information

If you are working with SQL Server, you often encounter this alert stating, See the SQL Server error log for more information. This is because error logs are useful for locating any issues in the process.

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

Conclusion

In this tutorial, we have already seen multiple methods for viewing and locating error logs in SQL Server.

You may like SQL server tutorials.

• SQL Server logical operators and example
• Create a table from view in SQL Server
• Loop in SQL Server stored procedure
• Transaction count after EXECUTE indicates a mismatching number of BEGIN and COMMIT statements