Technical Article

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

Rate

5 (1)

Share

Share

Rate

5 (1)