Querying SQL Server Error Log

SQL Server error log offers good way to obtain information when troubleshooting SQL Server related problem. A while back, I wrote a blog post about reading SQL Server error log using Microsoft Log Parser. That blog post can be found here. There are many ways in which you can query the SQL Server error log. One of them is using the sys.sp_readerrorlog stored procedure. This stored procedure can be located in the master database. It accepts 4 input parameters:

1. @p1 – integer: This parameter is to specify which error log to read. SQL Server error log would rollover. By default, SQL Server error log would keep a file for the current log and maximum 6 of archived logs (this setting can be changed easily), ERRORLOG, ERRORLOG.1, ERRORLOG.2, ERRORLOG.3, ERRORLOG.4, ERRORLOG5 and ERRORLOG6. ERRORLOG is where SQL Server stores the current error log, ERRORLOG.1 is where SQL Server stores the most recent archived, etc. If we put 0 or null on this parameter, we are querying the current error log (ERRORLOG). 1 would refer to ERRORLOG.1. The same concept would apply to SQL Server Agent error log.
2. @p2 – integer: This parameter is to specify if we want to query the SQL Server Error Log or the SQL Server Agent Error Log. If we enter 1 or null, we are querying the SQL Server Error Log. However, if we enter 2, then we are querying the SQL Server Agent Error Log.
3. @p3 – varchar(255): We can specify word or phrase that we are looking within the text/message on the SQL Server error log or SQL Server Agent error log.
4. @p4 – varchar(255): We can specify word or phrase that we are looking within the text/message on the SQL Server error log or SQL Server Agent error log. If we enter a word or phrase on @p3 parameter and enter another word or phrase on @p4 parameter, the stored procedure should return error log entries that contain both words/phrases (AND operator). If we leave @p3 blank but enter a word or phrase on @p4, the stored procedure would not filter the error log. It will ignore the @p4 parameter filter.

Some Usage Examples

The following would return all entries on the current SQL Server error log (ERRORLOG):

EXEC sp_readerrorlog

or:

EXEC sp_readerrorlog 0

or:

EXEC sp_readerrorlog NULL, NULL, NULL, NULL

The following would return all entries on the current SQL Server Agent error log (SQLAGENT.OUT):

EXEC sp_readerrorlog 0, 2

The following would return entry from SQL Server error log when the SQL Server was starting the msdb database (in this case it was part of the server start up):

EXEC sp_readerrorlog 0, 1, 'starting', 'msdb'

This would returns:

Wait, There’s more…

If we look at the sp_readerrorlog stored procedure code closely, it is actually calling the xp_readererrorlog extended stored procedure. The xp_readerrorlog actually accepts more input parameter than the 4 input parameters described above. The following blog article described the parameters that xp_readerrorlog would accept. Basically it would accept 3 additional parameters:

1. Log date from range – Date time: this parameter would help to filter the log entries from a specific time period.
2. Log date to range – Date time: this parameter would help to filter the log entries to a specific time period.
3. Ascending or Descending – Varchar: this parameter can be use to specify the sorting order of the log entries based on the log date. Enter ‘asc’ for ascending order, and ‘desc’ for descending order.

So, for example, if we want to get the list of current SQL Server error log entries between 6:27 PM and 6:28 PM today (7th October 2012), and list the log entries in the descending log date order, I can use the following query:

EXEC xp_readerrorlog 0, 1, NULL, NULL, '2012-10-07 18:27', '2012-10-07 18:28', 'desc'

Related Notes:

• Querying SQL Error Log with Microsoft LogParser
• Querying SQL Server Agent Error Log with Microsoft LogParser