Multiple SQL Server Error log search

In Yesterday’s post I mentioned creating “registered servers” in groups in your “Registered Servers” Window.  Today, i present a short script, which, if applied to the SQL 2005 (and up) groups  will allow you to in real-time query all the SQL servers error logs…searching for suspicious things of your choice… just change the ‘where’ clause below…


/* Steve Schneider    19 May 2011       I have left a #ErrLogs … i might find future use for it.  */

IF  OBJECT_ID(‘Tempdb..#ErrLogs’)isnotnull        DROPTABLE #ErrLogs

IF  OBJECT_ID(‘Tempdb..#LogDetails’)isnotnull      DROPTABLE #LogDetails

declare @LogFileAgeInDays int

createtable #ErrLogs (Archive int, ArcDate smalldatetime, FileSize bigint)

createtable #LogDetails (logdate datetime, ProcessInfo char(12),MessageText varchar(4000))



      insert #LogDetails   execmaster.dbo.xp_readerrorlog

      insert #LogDetails   execmaster.dbo.xp_readerrorlog 1

      insert #LogDetails   execmaster.dbo.xp_readerrorlog 2

      insert #LogDetails   execmaster.dbo.xp_readerrorlog 3

      insert #LogDetails   execmaster.dbo.xp_readerrorlog 4

      insert #LogDetails   execmaster.dbo.xp_readerrorlog 5

      insert #LogDetails   execmaster.dbo.xp_readerrorlog 6


insert #ErrLogs execxp_enumerrorlogs

select @LogFileAgeInDays =datediff(dd,min(logdate),max(logdate))from #logdetails


      @LogFileAgeInDays as LogFileAgeInDays,*

from #LogDetails


      messagetext like‘%I/O requests taking longer than 15%’or

      messagetext like‘%begin stack dump%’

orderby logdate desc

droptable #LogDetails

droptable #ErrLogs


One Reply to “Multiple SQL Server Error log search”

  1. I don’t even know how I ended up here, but I thought this post was good. I don’t know who you are but certainly you’re going to a famous blogger if you aren’t already 😉 Cheers!

Leave a Reply

Your email address will not be published. Required fields are marked *