When SQL Server error log is not initialized at a regular interval causing the error log file to grow into a huge file. Viewing that in the SQL Log File Viewer would be a time consuming task. 

This article will show how to view the SQL Server error log from a query analyzer in 2000 or query editor in later versions. As an additional benefit this code filters the log file and displays only the past 24 hours (default code). 

To view SQL Server error logs in SQL Server 2000

DECLARE @HOURS INT
SET @HOURS = 24

CREATE TABLE #ErrorLog
(ErrorLog Varchar(1000),
ContinuationRow INT)

INSERT INTO #ErrorLog
EXEC sp_readerrorlog

DELETE FROM #ErrorLog
WHERE
(LEFT(LTRIM(ErrorLog),4)
NOT LIKE DATEPART(YYYY,GETDATE())
AND ContinuationRow = 0)
OR ContinuationRow = 1
OR LEN(ErrorLog) < 25
OR
(CAST(LEFT(LTRIM(ErrorLog),23) AS DATETIME)
< CAST(DATEADD(HH,-@HOURS,GETDATE()) AS VARCHAR(23)))

SELECT * FROM #ErrorLog 

DROP TABLE #ErrorLog

To view SQL Server error logs in SQL Server 2005 / 2008 / 2008R2

DECLARE @HOURS INT
SET @HOURS = 24

CREATE TABLE #ErrorLog
(LogDate DateTime, ProcessInfo Varchar(50),
[Text] Varchar(4000))

INSERT INTO #ErrorLog
EXEC sp_readerrorlog

DELETE FROM #ErrorLog
WHERE LogDate < CAST(DATEADD(HH,-@HOURS,
GETDATE()) AS VARCHAR(23))

SELECT * FROM #ErrorLog 

DROP TABLE #ErrorLog

The script can be changed to display only data for the past n # of hours by changing the value of @Hours.

Do you like this site? Like our FB page @ Facebook.com\LearnSQLWithBru so that, you know when there is a new blog post.

—Bru Medishetty