I came across an interesting situation recently where a SQL instance had been configured with the Audit of successful and failed logins being written to the errorlog.

ie

This meant… every time a user or the application connected to the SQL instance – an entry was written to the errorlog.

This meant…  huge SQL Server errorlogs.

Opening an errorlog in the usual way, using SQL management studio, was extremely slow…

Luckily, I was able to use xp_readerrorlog to work around this – here’s some example queries..

 

To show errorlog entries from the currently active log, just for today :-

DECLARE @now DATETIME

DECLARE @midnight DATETIME

SET @now = GETDATE()

SET @midnight =  DATEADD(d, DATEDIFF(d, 0, getdate()), 0)

EXEC xp_readerrorlog 0,1,NULL,NULL,@midnight,@now

 

To find out how big the current errorlog actually is, and what the earliest and most recent entries are in the errorlog :-

CREATE TABLE #temp_errorlog (Logdate DATETIME, ProcessInfo VARCHAR(20),Text VARCHAR(4000))

INSERT INTO #temp_errorlog EXEC xp_readerrorlog 0 -- for current errorlog
SELECT COUNT(*) AS 'Number of entries in errorlog', MIN(logdate) AS 'ErrorLog Starts', MAX(logdate) AS 'ErrorLog Ends'
FROM #temp_errorlog

DROP TABLE #temp_errorlog

To show just DBCC history  information in the current errorlog :-

EXEC xp_readerrorlog 0,1,'dbcc'

 

To show backup errorlog entries in the current errorlog :-

CREATE TABLE #temp_errorlog (Logdate DATETIME, ProcessInfo VARCHAR(20),Text VARCHAR(4000))

INSERT INTO #temp_errorlog EXEC xp_readerrorlog 0 -- for current errorlog
SELECT * from #temp_errorlog WHERE ProcessInfo = 'Backup' ORDER BY Logdate

DROP TABLE #temp_errorlog

XP_Errorlog is an undocumented system stored procedure – so no official Microsoft link describing the parameters it takes – however,  there’s a good blog on this here

And, if you do have a problem with huge errorlogs – please consider running system stored procedure  sp_cycle_errorlog on a nightly or regular basis.  But if you do this,  remember to change the amount of errorlogs you do retain – the default of 6 might not be sufficient for you….