» » » » Xp_readerrorlog in SQL Server 2012

Xp_readerrorlog in SQL Server 2012

, , Edit

I have always been a big fan of Xp_readerrorlog. The simple reason is that it much faster to read errorlog using the script compared to using SSMS. xp_readerrorlog gives more options to filter the log for specific dates or containing specific string etc

In SQL Server 2012, xp_readerrorlog has undergone a minor change. if you use xp_readerrorlog as used before you may encounter the following error

for ex: -
 
EXEC master.dbo.Xp_readerrorlog
  0,
  1,
  '',
  '',
  '20130502 00:00',
  '20130503' 

throws a error  saying

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

If you are like me, who likes to capture the results of errorlog into a table then you may get a different error.

for ex:
 
INSERT INTO #read_error_log
EXEC master.dbo.Xp_readerrorlog
  0,
  1,
  '',
  '',
  '20130502 00:00',
  '20130503' 


Msg 0, Level 11, State 0, Line 0
A severe error occurred on the current command. The results, if any, should be discarded.

The reason for the error is that the third and the fourth parameter, to be passed as a search strings strictly accepts nvarchar Datatype. Adding the letter 'N'; makes the parameter into nvarchar as shown below .
 
EXEC master.dbo.Xp_readerrorlog
  0,
  1,
  N'',
  N'',
  '20130502 00:00',
  '20130503' 

The script provided below will help one read the error log quickly and effectively. The script is similar to the one shared in this blog previously over here. Instead of using sp_readerrorlog, the script below uses xp_readerrorlog with additional parameters which makes it even more effective. sp_readerrorlog doesn't provide time based filters (6th & 7th parameters) which are the most important filters while reading errorlog.
 
CREATE TABLE #read_error_log
  (
     logdate      DATETIME,
     processinfo  VARCHAR(200),
     errorlogtext VARCHAR(max)
  )

INSERT INTO #read_error_log
EXEC master.dbo.Xp_readerrorlog
  0,
  1,
  N'',
  N'',
  '20130502 00:00',
  '20130503'

SELECT *
FROM   #read_error_log
ORDER  BY logdate DESC

TRUNCATE TABLE #read_error_log

DROP TABLE #read_error_log