This article explores the xp_readerrorlog command for reading SQL Server error logs using T-SQL.
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:
You can use SERVERPROPERTY() command to know the current error log file location
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:
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
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:
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:
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:
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:
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:
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:
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';
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.
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 email@example.com
View all posts by Rajendra Gupta