Errorlog Notification
This script Notifies the user(s) whenever there is an Error logged in the Error message.
It excludes the Error messages 15457 and 1073759806. These 2 Errors can be ignored. (I know we like to keep our Elogs clean but for some time you need to yield to them J ). You’ll see the first error when you check the Server props in the EM and other is when you start the sql mail manually.
This script assumes that you already configured SQL MAIL.
This script creates 2 tables and a temp table. Usually I create all my monitoring scripts in one database (DBADMIN) that makes my things to keep simple and easy. So I will leave the decision to you folks, if you don’t want create database (DBADMIN) replace the database name with your database name.
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[Errors1]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[Errors1]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[Errors]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[Errors]
GO
CREATE TABLE [dbo].[Errors1] (
[Error_Message] [varchar] (50) NULL ,
[Error_Date] [datetime] NULL ,
[SPID] [varchar] (15) NULL ,
[Servername] [varchar] (20) NULL
) ON [PRIMARY]
GO
CREATE TABLE [dbo].[Errors] (
[Error_Message] [varchar] (50) NULL ,
[Error_Date] [datetime] NULL ,
[SPID] [varchar] (15) NULL ,
[Servername] [varchar] (20) NULL
) ON [PRIMARY]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[Usp_Errorlog_Notification]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[Usp_Errorlog_Notification]
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO
CREATE PROCEDURE dbo.Usp_Errorlog_Notification AS
DECLARE @Error_Message varchar(75)
DECLARE @Error_Date datetime
DECLARE @SPID varchar(15)
DECLARE @cnt int
DECLARE @Cnt1 int
DECLARE @Row_Count int
DECLARE @MxDate Datetime
DECLARE @ErrorMsg_Message varchar(255)
CREATE TABLE #Errors (vchMessage varchar(255), ID int)
INSERT #Errors Exec master..xp_readerrorlog
SELECT @Row_Count = count(*) FROM #Errors WHERE vchMessage LIKE '%Error:%' AND
vchMessage NOT LIKE '%15457%' AND vchMessage NOT LIKE '%1073759%'
If @Row_Count <> 0
BEGIN
DECLARE ErrorMsg CURSOR For Select vchMessage from #Errors Where vchMessage LIKE '%Error:%' AND
vchMessage NOT LIKE '%15457%' AND vchMessage NOT LIKE '%1073759%'
OPEN ErrorMsg
FETCH NEXT from ErrorMsg into @ErrorMsg_Message
Select @Cnt = count(*) from Errors
If @Cnt = 0
Begin
WHILE ( @@fetch_status = 0 )
BEGIN
Select @Error_Message = Substring(@ErrorMsg_Message, 34,39) from #Errors --where @ErrorMsg_Message LIKE '%Error:%'
Print @Error_Message
SElect @Error_Date = Substring (@ErrorMsg_Message,1,22) from #Errors
Select @SPID = Substring(@ErrorMsg_Message, 24,6) from #Errors
Insert into Errors values (@Error_Message, @Error_Date, @SPID, @@SERVERNAME)
FETCH NEXT from ErrorMsg into @ErrorMsg_Message
End
EXEC master..xp_sendmail
@recipients = '<Recipients_List>'
,@message = ' Error(s) found on the Server <Server_Name>'
,@subject = 'Error Notification ON <Server_Name>'
,@query = 'Select * from DBADMIN..Errors'
,@attach_results = 'TRUE'
,@width = 300
End
ELSE
Print 'Going Errors1'
BEGIN
WHILE ( @@fetch_status = 0 )
BEGIN
Select @Error_Message = Substring(@ErrorMsg_Message, 34,39) from #Errors --where @ErrorMsg_Message LIKE '%Error:%'
Print @Error_Message
SElect @Error_Date = Substring (@ErrorMsg_Message,1,22) from #Errors
print @Error_Date
Select @SPID = Substring(@ErrorMsg_Message, 24,6) from #Errors
Print @SPID
Insert into Errors1 values (@Error_Message, @Error_Date, @SPID, @@SERVERNAME)
FETCH NEXT from ErrorMsg into @ErrorMsg_Message
Select @MxDate = Max(Error_Date) from Errors
Select @cnt1 = Count(*) from Errors1 where Error_Date > ( Select Max(Error_Date) from Errors)
If @Cnt1 <> 0
BEGIN
Drop table Errors
Select * into Errors from Errors1 where Error_Date > @MxDate
EXEC master..xp_sendmail
@recipients = '<Recipients_List>'
,@message = ' Error(s) found on the Server <Server_Name>'
,@subject = 'Error Notification ON <Server_Name>'
,@query = 'Select * from DBADMIN..Errors1'
,@attach_results = 'TRUE'
,@width = 300
End
Truncate table Errors1
End
End
Close ErrorMsg
Deallocate ErrorMsg
Drop table #Errors
END
ELSE
Print 'No Errors in the Error Logs'
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO 
