Rajendra Gupta
Reads current SQL Server error log and search for text 'Warning'

Read SQL Server error logs using the xp_readerrorlog command

November 14, 2019 by

This article explores the xp_readerrorlog command for reading SQL Server error logs using T-SQL.

Introduction

SQL Server error logs are useful in troubleshooting issues with the database server. It contains both system and user-defined events information. We can use error logs in the following scenario:

  • Information about SQL Server instance
  • Audit login – Success or failure
  • Database recovery status
  • Logged errors messages

We can read the error logs using different methods, but firstly, we should know the path of the error logs.

SSMS GUI method

Connect to a SQL instance in SSMS and navigate to Management. Expand the Management folder, followed by SQL Server Logs. In the SQL Server Logs, you can see different logs – Current and Archive:

SQL Server error logs

Double-click on the desired error log file and it will open the error log in a separate window:

View error log in SSMS

T-SQL method

You can use SERVERPROPERTY() command to know the current error log file location

Error log location

You can browse to the directory and it shows available error log files:

  • ErrorLog: Current error log file
  • ErrorLog.1: Archive error log file 1
  • ErrorLog.2: Archive error log file 2

View error log in the log folder

Problem with accessing error log using GUI

Usually DBA uses the GUI method for reading the error log as shown above. You might face an issue where you try to open the error log, but it takes much time in opening it. Further, it gives a timeout issue. It might happen due to a huge error log.

  • Usually, we deploy a SQL Agent job to recycle error log files so that it does not grow huge. If the job is not available or not running, it might result in a substantial error log
  • If we have enabled auditing for successful logins, SQL Server logs an entry for each successful database connection. In a busy OLTP system, you might get many successful logins that can flood error logs
  • SQL Server log backups. Suppose you have 50 databases in SQL instance. You configured transaction log backup every 10 minutes. Therefore, every 10 minutes, it writes information about log backups for each database in the error logs. We can suppress these messages, but it is not in the scope of this article

If we want to search through multiple error logs, it can be a frustrating and challenging task to read and filter logs using GUI.

Query to list error log and their sizes

You can execute the following query to list down available error logs along with their sizes:

Query to list error log and their sizes

Here the date column shows the timestamp of last entry in the particular file.

Read SQL Server log files using xp_readerrorlog

We use extended stored procedure xp_readerrorlog to read SQL Server error log files.

Syntax for xp_ReadErrorLog command

  • LogNumber: It is the log number of the error log. You can see the lognumber in the above screenshot. Zero is always referred to as the current log file
  • LogType: We can use this command to read both SQL Server error logs and agent logs
    • 1 – To read the SQL Server error log
    • 2- To read SQL Agent logs
  • SearchItem1: In this parameter, we specify the search keyword
  • SearchItem2: We can use additional search items. Both conditions ( SearchItem1 and SearchItem2) should be satisfied with the results
  • StartDate and EndDate: We can filter the error log between StartDate and EndDate
  • SortOrder: We can specify ASC (Ascending) or DSC (descending) for sorting purposes

Let’s understand command xp_readerrorlog using the following examples.

Read Current error log

We can read the current SQL Server error log without specifying any parameter:

Read Current error log

Read SQL Server error log

In the following command, we use the following parameters:

  • 0: To specify the current error log file
  • 1: Specify a value for parameter LogType as specified above

Read SQL Server error log

Read SQL Server agent log

In this command, we specify a value for LogType parameter 2 that refers to SQL Server agent logs:

Read SQL Server agent log

Reads current SQL Server error log and search for text ‘Warning’

This command searches for text warning in the current SQL Server error log file:

Reads current SQL Server error log and search for text 'Warning'

We need to put the text in single quotes with N. We might get following error message with N. It declares the string as Nvarchar data type instead of Varchar:

Msg 22004, Level 12, State 1, Line 0
Error executing extended stored procedure: Invalid Parameter Type

Error message

Reads current SQL Server error log and search for multiple conditions

This query searches for text Database and Initialization in the current SQL Server error logs. Both the conditions should satisfy to return results:

search for multiple conditions

Read current SQL Server error log for a specific duration

Suppose we want to search SQL Server error logs for a specific duration. Suppose we want to search for keyword recovery between 7th November 2019 midnight and 9 AM. We can specify the start and end date parameter in the following format:

Server error log for a specific duration

In the output, you can see the result is sorted in the ascending order from the start date to the end date. We can specify parameter desc so that results are sorted in descending order from an end date to start date:

Server error log for a specific duration in desending order

Read current SQL Server error log for a specific duration, multiple conditions using variables

We can use the following query for specifying the required parameter in the variable and use these variables in the command xp_readerrorlog.

It uses the following conditions:

  • Search keywords Recovery and MSDB
  • Specify start and end date

Read current SQL Server error log using variables with xp_readerrorlog command

Conclusion

This article explores the methods for reading SQL Server error logs using xp_readerrorlog command. It helps to troubleshoot issues in SQL Server and avoid the pain of reading large error logs from GUI for specific conditions.

Rajendra Gupta

Rajendra Gupta

Rajendra has 8+ years of experience in database administration having a passion for database performance optimization, monitoring, and high availability and disaster recovery technologies, learning new things, new features.

While working as a Senior consultant DBA for big customers and having certified with MCSA SQL 2012, he likes to share knowledge on various blogs.
He can be reached at rajendra.gupta16@gmail.com

View all posts by Rajendra Gupta
Rajendra Gupta

Latest posts by Rajendra Gupta (see all)

365 Views