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