Automate Monitoring SQL Server Error Logs with Email Alerts

CREATE PROCEDURE [dbo].[SQL_ErrorLog_Alert]
@Minutes [int] = NULL  
AS
BEGIN
SET NOCOUNT ON;
DECLARE @ERRORMSG varchar(8000)
DECLARE @SNO INT
DECLARE @Mins INT
DECLARE @SQLVERSION VARCHAR(4)
IF @Minutes IS NULL  -- If the optional parameter is not passed, @Mins value is set to 6
 SET @Mins = 6
ELSE
 SET @Mins = @Minutes
  /* Fetches the numeric part of SQL Version */
 SELECT @SQLVERSION = RTRIM(LTRIM(SUBSTRING(@@VERSION,22,5))) 

IF @SQLVERSION = '2000'  
 /* Checks the version of SQL Server and executes 
 the code depending on it since the output of the 
 sp_readerrorlog varies between SQL 2000 and the next versions */
 BEGIN
  
 /*Temporary table to store the output from execution of sp_readerrorlog */
  
 CREATE Table #ErrorLog2000 
 (ErrorLog varchar(4000),ContinuationRow Int) 
 INSERT INTO  #ErrorLog2000  -- Stores the output of sp_readerrorlog
 EXEC sp_readerrorlog
 /* The code below deletes the rows in the error log which are mostly 
 the SQL startup messages written into the error log */
  
 DELETE FROM #ErrorLog2000 
 WHERE (LEFT(LTRIM(ErrorLog),4) NOT LIKE DATEPART(YYYY,GETDATE()) 
    AND ContinuationRow = 0) 
 OR (ErrorLog LIKE '%Intel X86%')
 OR (ErrorLog LIKE '%Copyright %')
 OR (ErrorLog LIKE '%Microsoft %')
 OR (ErrorLog LIKE '%All rights reserved.%')
 OR (ErrorLog LIKE '%Server Process ID is %')
 OR (ErrorLog LIKE '%Logging SQL Server messages in file %')
 OR (ErrorLog LIKE '%Errorlog has been reinitialized%')
 OR (ErrorLog LIKE '%Starting up database %')
 OR (ErrorLog LIKE '%SQL Server Listening %')
 OR (ErrorLog LIKE '%SQL Server is ready %')
 OR (ErrorLog LIKE '%Clearing tempdb %')
 OR (ErrorLog LIKE '%Recovery %')
 OR (ErrorLog LIKE '%to execute extended stored procedure %')
 OR (ErrorLog LIKE '%Analysis of database %')
 OR (ErrorLog LIKE '%Edition%')
 OR LEN(ErrorLog) < 25 
 OR (CAST(LEFT(LTRIM(ErrorLog),23) AS DATETIME) 
  < CAST(DATEADD(MI,-@Mins,GETDATE()) AS VARCHAR(23)))

  /* Once the SQL Server startup and other information prior to 
  @Mins is deleted from the temporary table, the below code starts 
  concatenating the remaining rows in the temporary table 
  and stores into single variable */
  
 SELECT @ERRORMSG = COALESCE(@ERRORMSG + CHAR(13) , '')  
   + ErrorLog FROM #ErrorLog2000
  
 DROP TABLE #ErrorLog2000 
 END
ELSE
 BEGIN
 CREATE TABLE #ErrorLog2005 
 (LogDate DATETIME, ProcessInfo VARCHAR(50) ,[Text] VARCHAR(4000))
 INSERT INTO #ErrorLog2005 
 EXEC sp_readerrorlog
 DELETE FROM #ErrorLog2005 
 WHERE LogDate < CAST(DATEADD(MI,-@Mins,GETDATE()) AS VARCHAR(23))
 OR ([Text] LIKE '%Intel X86%')
 OR ([Text] LIKE '%Copyright%')
 OR ([Text] LIKE '%All rights reserved.%')
 OR ([Text] LIKE '%Server Process ID is %')
 OR ([Text] LIKE '%Logging SQL Server messages in file %')
 OR ([Text] LIKE '%Errorlog has been reinitialized%')
 OR ([Text] LIKE '%This instance of SQL Server has been using a process ID %')
 OR ([Text] LIKE '%Starting up database %')
 OR ([Text] LIKE '%SQL Server Listening %')
 OR ([Text] LIKE '%SQL Server is ready %')
 OR ([Text] LIKE '%Clearing tempdb %')
 OR ([Text] LIKE '%to execute extended stored procedure %')
 OR ([Text] LIKE '%Analysis of database %')
 OR ProcessInfo = 'Backup' -- Deletes backup information
 SELECT  @ERRORMSG = COALESCE(@ERRORMSG + CHAR(13) , '') 
   + CAST(LogDate AS VARCHAR(23)) + '  ' 
   + [Text] FROM #ErrorLog2005
  
 DROP TABLE #ErrorLog2005
 END
IF @ERRORMSG IS NOT NULL 
  -- There is some data in SQL error log that needs to be stored
 BEGIN
  IF  EXISTS (SELECT * FROM dbo.sysobjects 
    WHERE  id = OBJECT_ID(N'[dbo].[SQL_ErrorLog]') 
    AND OBJECTPROPERTY(id, N'IsUserTable') = 1)
   
   INSERT INTO [dbo].[SQL_ErrorLog]
   SELECT @ERRORMSG
   
  ELSE
   
  BEGIN
   
  CREATE TABLE [dbo].[SQL_ErrorLog](
   [TEXT] [varchar](8000) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
   ) ON [PRIMARY]
  INSERT INTO [dbo].[SQL_ErrorLog]
  SELECT @ERRORMSG
   
  END
 END
ELSE  -- No error messages have been in the last @Mins minutes
 Print 'No Error Messages'
END
GO

CREATE PROCEDURE [dbo].[SQL_ErrorLog_Alert_Notify]
@LKDSVRNAME VARCHAR(128) = NULL
AS
BEGIN
SET NOCOUNT ON;
DECLARE @SUBJECT  VARCHAR(8000)
DECLARE @MSGBODY  VARCHAR(8000)
 IF @LKDSVRNAME IS NOT NULL  
    /* A linked server name is passed, so alert will be for the linked server.*/
  
 BEGIN
   
  CREATE TABLE #ERRTBL ([TEXT] VARCHAR(8000)) 
  /* Temporary table to poll the linked server 
  and store the error data to be used in the cursor, in the next step */
 
  INSERT INTO #ERRTBL
  EXEC('SELECT [Text] FROM ' + @LKDSVRNAME + '.AlertsDB.DBO.SQL_ErrorLog')
  
  SET @SUBJECT = @LKDSVRNAME + ' SQL-SERVER ERROR LOG SUMMARY'
  DECLARE CURSOR1 CURSOR
  FOR SELECT [TEXT] FROM #ERRTBL
  
  OPEN CURSOR1
  FETCH NEXT FROM CURSOR1
  INTO @MSGBODY
  WHILE @@FETCH_STATUS = 0
  BEGIN
    EXEC msdb.dbo.sp_send_dbmail
    @profile_name = 'SQL ALERTING PROFILE', -- Modify the profile name
    @recipients = '[email protected]',-- Modify the email
    @body = @MSGBODY, 
    @subject = @SUBJECT ; 
  FETCH NEXT FROM CURSOR1
  INTO @MSGBODY
  END 
  CLOSE CURSOR1
  DEALLOCATE CURSOR1 
  EXEC ('DELETE FROM ' + @LKDSVRNAME + '.AlertsDB.DBO.SQL_ErrorLog') 
     
     -- Modify the database name
     
  DROP TABLE #ERRTBL
   
 END
  ELSE  
  /* A linked server name is not passed, 
  so alert will be for the local server. */
   
 BEGIN
    
  SET @SUBJECT = @@SERVERNAME + ' SQL-SERVER ERROR LOG SUMMARY'
  DECLARE CURSOR1 CURSOR
  FOR SELECT [TEXT] FROM AlertsDB.DBO.SQL_ErrorLog
  OPEN CURSOR1
  FETCH NEXT FROM CURSOR1
  INTO @MSGBODY
  WHILE @@FETCH_STATUS = 0
  BEGIN  
    EXEC msdb.dbo.sp_send_dbmail
    @profile_name = 'SQL ALERTING PROFILE', -- Modify the profile name
    @recipients = '[email protected]',-- Modify the email
    @body = @MSGBODY, 
    @subject = @SUBJECT ; 
 
  FETCH NEXT FROM CURSOR1
  INTO @MSGBODY
  END 
  CLOSE CURSOR1
  DEALLOCATE CURSOR1   
  DELETE FROM AlertsDB.DBO.SQL_ErrorLog   -- Modify the database name
 END
END

EXEC [dbo].[SQL_ErrorLog_Alert_Notify] 
EXEC [dbo].[SQL_ErrorLog_Alert_Notify] @LKDSVRNAME = 'LinkedServer1'
EXEC [dbo].[SQL_ErrorLog_Alert_Notify] @LKDSVRNAME = 'LinkedServer2'