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))

 

Begin

      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

End

insert #ErrLogs execxp_enumerrorlogs

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

select

      @LogFileAgeInDays as LogFileAgeInDays,*

from #LogDetails

where

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

      messagetext like‘%begin stack dump%’

orderby logdate desc

droptable #LogDetails

droptable #ErrLogs

 

Leave a Reply

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