Reading SQL Error log

SQL Error log can be read from SQL Server's management studio. However, Management studio is too slow and definitely not the greatest way of taking a quick look at SQL Error log. Sp_readerrorlog is definitely a much better command which can help us read a error log must faster way. Also , the script below Dumps the read error log into a temporary table. Once dumped one can use different kinds of filters as per our needs.

The script below loads error log into a temporary table, filters for a particular date range, removes error log entries for backup, and searches only for genuine error on the error log.


CREATE TABLE #error_log_dt
  (
     logdate     DATETIME,
     processinfo VARCHAR(30),
     text_data   VARCHAR(MAX)
  )

INSERT INTO #error_log_dt
EXEC Sp_readerrorlog

SELECT *
FROM   #error_log_dt
WHERE  logdate BETWEEN '20110815' AND '20110821'
       AND processinfo != 'backup'
       AND text_data LIKE '%error%'

DROP TABLE #error_log_dt