Read SQL Server error logs using the xp

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:

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

T-SQL method

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

1	SELECT SERVERPROPERTY('ErrorLogFileName') AS 'Error log file 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

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:

1	EXEC sys.sp_enumerrorlogs;

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

1	Exec xp_ReadErrorLog <LogNumber>, <LogType>, <SearchItem1>, <StartDate>, <EndDate>, <SortOrder>

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:

1	EXEC xp_ReadErrorLog

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 agent log

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

1	EXEC xp_ReadErrorLog 0,2

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:

1	EXEC xp_ReadErrorLog 0, 1, N'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

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:

1	EXEC xp_ReadErrorLog 0, 1, N'Database',N'Initialization'

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:

EXEC xp_readerrorlog

N'Recovery',

N'',

N'2019-11-07 00:00:01.000',

N'2019-11-07 09:00:01.000'

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:

EXEC xp_readerrorlog

N'Recovery',

N'',

N'2019-11-07 00:00:01.000',

N'2019-11-07 09:00:01.000',

N'desc'

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

DECLARE @logFileType SMALLINT= 1;

DECLARE @start DATETIME;

DECLARE @end DATETIME;

DECLARE @logno INT= 0;

SET @start = '2019-11-07 00:00:01.000';

SET @end = '2019-11-07 09:00:00.000';

DECLARE @searchString1 NVARCHAR(256)= 'Recovery';

DECLARE @searchString2 NVARCHAR(256)= 'MSDB';

EXEC master.dbo.xp_readerrorlog

@logno,

@logFileType,

@searchString1,

@searchString2,

@start,

@end;

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.

Author
Recent Posts

Rajendra Gupta

Hi! I am Rajendra Gupta, Database Specialist and Architect, helping organizations implement Microsoft SQL Server, Azure, Couchbase, AWS solutions fast and efficiently, fix related issues, and Performance Tuning with over 14 years of experience.

I am the author of the book "DP-300 Administering Relational Database on Microsoft Azure". I published more than 650 technical articles on MSSQLTips, SQLShack, Quest, CodingSight, and SeveralNines.

I am the creator of one of the biggest free online collections of articles on a single topic, with his 50-part series on SQL Server Always On Availability Groups.

Based on my contribution to the SQL Server community, I have been recognized as the prestigious Best Author of the Year continuously in 2019, 2020, and 2021 (2nd Rank) at SQLShack and the MSSQLTIPS champions award in 2020.

Personal Blog: https://www.dbblogger.com
I am always interested in new challenges so if you need consulting help, reach me at rajendra.gupta16@gmail.com

View all posts by Rajendra Gupta

Latest posts by Rajendra Gupta (see all)

Understanding PostgreSQL SUBSTRING function - September 21, 2024
How to install PostgreSQL on Ubuntu - July 13, 2023
How to use the CROSSTAB function in PostgreSQL - February 17, 2023