Thursday, August 25, 2011

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

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

DROP TABLE #error_log_dt