Xp_readerrorlog is a SQL Server extend stored procedure allows you read the contents of the SQL Server error log files.
It allows you to read the SQL Server and SQL Server Agent error logs.
SYNTAX
EXEC xp_ReadErrorLog <log_number>, <log_type>, <search_term1>, <search_term2>, <start_date>, <end_date>, <sort_order>
<log_number> Is a log number 0, 1, 2, 3.. of error log files. Where 0 returns the current log. 1 returns logs from file ERRORLOG.1.
<log_type> Is a type of error file that yo want to read, 1 to read SQL Server error logs and 2 to read SQL Server Agent error logs.
<search_term1> Is a search keyword for text column.
<search_term2> Is a additional search keyword for text column. If specified both<search_term1> and <search_term2>, then it only returns lines containing both terms.
<start_date> Start reading logs from this date.
<end_date> Reads logs till this date.
Lets see how you can read the error log files using xp_readerrorlog stored procedure.
Before reading the error log files using stored procedure xp_readerrorlog , you can also find and read the error log files directly from SQL Server Management Studio.
SQL Server error logs:
Go to Management folder then extend the SQL Server Logs folder there you can see all the error logs files.
To read the file, you can double click on any of file you wish to read.
Once you double click on file, you will see a log file viewer dialog box opens.
Within the log file viewer you can read the error logs as show below.
SQL Server Agent error log files:
Lets use the stored procedure xp_readerrorlog for reading the error logs.
Using xp_readerrorlog to read the SQL Server error logs
Lets read the current error logs using xp_readerrorlog, you can simply execute the stored procedure with out specifying any parameter.
EXEC xp_ReadErrorLog
As you can see, it returns the current error log.
Lets read the SQL Server error log from ERRORLOG.2 file.
In the following statement, have specified the two parameters to stored procedure xp_readerrorlog.
First parameter value as 2, to reading the log from file ERRORLOG.2 files and second parameter value as 1, to reading the SQL Server Log.
EXEC xp_ReadErrorLog 2, 1
As you can see, it returns the SQL Server error log from file ERRORLOG.2.
Filter the error logs
Lets reads the SQL Server current error log and also filter the error log for text ‘instance‘.
EXEC xp_ReadErrorLog 0, 1, N'instance'
As you can see, it returns error log of current file and filter the log for text ‘instance’.
Lets reads the SQL Server error log file ERRORLOG.1 and filter the error log for text ‘tempbd‘ and ‘database’.
EXEC xp_ReadErrorLog 2, 1, N'tempdb', N'database'
Lets read the error log file ERRORLOG.2 and filter the logs for text ‘instance’ and log starting and end dates between ‘2020-10-14 09:44:09.830’ and ‘2020-10-15 11:48:41.660’.
In following statement, we have not specified the value for fourth parameter that is additional search term as we want to filter error log for text ‘instance’ only so no need to provide the value for second search term, so specified the null value for it.
EXEC xp_ReadErrorLog 2, 1, N'instance', NULL, '2020-10-14 09:44:09.830' , '2020-10-15 11:48:41.660'
Sorting the error log
Lets read the SQL Server current error log and sort it in descending order.
Note that, In following statement we have not specified the values for search terms parameters, and start and end dates values to the store procedure xp_readerrorlog as we just want to read and sort the SQL Server current error log.
EXEC xp_ReadErrorLog 0, 1,NULL, NULL, NULL, NULL,'DESC'
Using xp_readerrorlog to read the SQL Server Agent logs
Lets read the SQL Server Agent current error log file.
To read the SQL Server Agent log, you need to specify second parameter value as 2.
EXEC xp_ReadErrorLog 0, 2
Lets read the SQL Server Agent error log file ERRORLOG.2 and filter the error log for text ‘database’.
EXEC xp_ReadErrorLog 2, 2, N'Database'
Lets read and filter and sort the SQL Server Agent current log in descending order.
Following statement, uses xp_readerrorlog stored procedure which returns the SQL Server Agent current log also filter the log for ‘database’ and sort in descending order
EXEC xp_ReadErrorLog 0, 2, N'Database', NULL, NULL, NULL,'DESC'
Recommend for you
SQL Server System Stored Procedures
SQL Server Startup procedures Sp_Procoption
