Learn more about SQL Server tools

mssqltips logo
 

Tutorials          DBA          Dev          BI          Career          Categories          Webcasts          Whitepapers          Today's Tip          Join

Tutorials      DBA      Dev      BI      Categories      Webcasts

DBA    Dev    BI    Categories

 

Automate Monitoring SQL Server Error Logs with Email Alerts


By:   |   Read Comments (25)   |   Related Tips: 1 | 2 | 3 | 4 | 5 | 6 | More > Monitoring

New Whitepaper > 10 Ways We Can Steal Your Data >> download now


Problem

As a SQL Server DBA, monitoring SQL Server error logs is part of my daily routine.  Manually reviewing the error logs on each server is a time consuming process, especially when working on other issues. I wanted to review the error logs when there are entries in them which need my attention. How can I accomplish this task in an automated manner by receiving emails?  Read this tip to learn how to become a more proactive DBA.

Solution

Important diagnostic information is available in the SQL Server error logs. There are entries related to database backups and recovery, changes in the recovery model, unsuccessful logins, and DBCC messages are some of the various items logged in the SQL Server error log.  In this tip we will look at the steps to configure automatic email alerts when there is an entry in the SQL Server error log that may need to be addressed.  For this setup, I am using two stored procedures (one for monitoring and one for alerting) and a SQL Server which has Database Mail enabled, to notify via email alerts.

What does the monitoring stored procedure do?

The monitoring stored procedure's main functionality is to read the current SQL Server log into a temporary table and filter those records which are older than certain number of minutes. The data which is in the rows format in the temporary table is concatenated in the form of a paragraph and then finally inserted into a table.

How does the monitoring stored procedure work?

Let's take a look at how this monitoring stored procedure works. The stored procedure accepts an integer value as a parameter, which is used later to filter the data from the error log. Then it reads the current SQL Server error log and writes the data into a temporary table. The data in the temporary table is deleted when it is older than the minutes passed as parameter, or 6 minutes, from the current time. SQL Server startup messages and database backup entries written into error log file are also deleted.

The next step in the procedure stores the error log information in the temporary table to a local variable @ERRORMSG. In a period of 5 minutes, there can be multiple entries into the error log. I used COALESCE and CHAR(13) carriage return to retain the separate error messages in a new line when the alert is sent. Once the records in the temporary table are added into the variable, the temporary table is dropped. The same set of code repeats twice, that is because the sp_readerrorlog output varies in SQL Server 2000 and later versions. The next statement checks if the variable @ERRORMSG is null, if it is not null then there are some error log entries that have to be alerted, so it stores that value in a local table in the alerts database.

Creating the monitoring stored procedure

Use the code below to create the stored procedure and remember to make changes as per your system such as the Database Mail profile, database name where the table is to be created, email id's to be notified, etc.

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

Once the stored procedure is in place on all production servers, I created a SQL Server Agent Job that executes this stored procedure and scheduled it to execute at a 5 minute interval. My next step was to configure my alerting server to send the email alerts.

How does the alerting stored procedure work?

This stored procedure is created on the SQL Server where Database Mail is enabled.  It uses Linked Servers to pull the error log data and send alerts. For more information on Database Mail, please read the tips included under Next Steps at the end of this tip.

The alerting procedure has an optional parameter, when it is not passed; the procedure alerts the local server's error messages. When a linked server name is passed, that server's error messages are alerted, with the Linked Server name in the Subject (to identify which server had the error message).

A temporary table is created that would be used to store the record(s) from the Linked Server table. An Execute statement is used to insert the data into the temporary table. In the next step, a cursor is used in order to fetch the data from the temporary table and send an email alert. I am using the system stored procedure msdb.dbo.sp_send_dbmail to send the email alerts.  Once all the rows in the temporary table are processed and alerts are sent, the temporary table is dropped and the records in the Linked Server's table are deleted.

When I first started writing the procedure, I did not use the cursor and assumed there would be one record at any time (since my alerting procedure would poll every 5 minutes and delete the records after processing). But was later proven wrong when the alerting server was under maintenance for an hour or so and one of the servers had multiple error log entries in that time period.

Creating the alerting stored procedure

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 = 'dbagroup@yourdomain.com',-- 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 = 'dbagroup@yourdomain.com',-- 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

I mentioned in the code where you need to make changes before implementing, they are; "database name" where the SQL_ErrorLog table is created, "Database Mail profile" and the "recipients email address". This stored procedure is then scheduled on my alerting server at a 5 minute interval. The following code needs to be included in the job step (with Linked Server names).

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

Summary

In the past, error log entries were noticed at a later time (when looked at the SQL Server log manually at regular intervals), but never was an issue noticed within minutes of its occurrence. After configuring this automatic alerting, I view the error logs only when I get the alert. Often, these SQL Server error log alerts have helped me respond to issues immediately and accordingly. Error log entries that I have been notified about are:
  • Transaction log being full causing a database backup failure
  • An error such as SQL Server has encountered x number of occurrence(s) of I/O requests taking longer than 15 seconds to complete
  • DBCC CHECKDB printed errors
  • and so on.
Next Steps
  • Read the following Tip Setting up Database Mail for SQL 2005.
  • Read more Tips on Database Mail on MSSQL Tips here
  • Consider implementing the solution outlined in this tip for monitoring the SQL Server error logs to reduce the time needed on a daily basis to monitor your SQL Server instances and to make you a more proactive DBA.


Last Update:


signup button

next tip button



About the author





Post a comment or let the author know this tip helped.

All comments are reviewed, so stay on subject or we may delete your comment. Note: your email address is not published. Required fields are marked with an asterisk (*).

*Name    *Email    Notify for updates 


SQL tips:

*Enter Code refresh code     



Saturday, November 29, 2014 - 11:01:10 PM - Bru Medishetty Back To Top

Hi Viplove,

In response to your comment. You will not get emails every 5 minutes with the same message, as you see the script reads only the last 6 minutes of the SQL Server Error Log, so at 11 AM, it will alert any error log events between 10:54 and 11 and at 11:05, it will look for the entries on or after 10:59 and so on...


Friday, November 28, 2014 - 1:52:31 PM - Viplove Back To Top

Fantastic script but I see one issue. If you run this script every 5 minutes without clearing the SQL error logs you are going to get an email every five minutes with the same error message.


Tuesday, July 29, 2014 - 10:17:25 AM - Junior DevDBA Back To Top

Just a question about @LKDSVRNAME - given that the linked server is being used for what looks like dynamic SQL, might it be better to make that input parameter use the sysname data type instead of plain varchar(128)? I think the sysname data type would disallow characters that might be used for SQL injection.

Just wondering.

Thanks,
Junior DevDBA


Thursday, September 05, 2013 - 7:00:20 PM - Garyreeds Back To Top

This script is not sending email when there is an backup failure error in error log 

For example, I can see the below error in error log but the script is unable to send this error inforamtion in email.. Please advise

Error: 3041, Severity: 16, State: 1.

2013-09-05 15:18:52.95 Backup      BACKUP failed to complete the command BACKUP DATABASE admi. Check the backup application log for detailed messages.


Tuesday, March 12, 2013 - 1:20:18 AM - JC Back To Top

Hi Bru,

Thanks for the post, it works for me on an instance in a SQL cluster.

1. Is it possible to put multiple email addresses in the recipients? What is the syntax? A comma between email addresses?

2. Do I have to do this on each instance on the cluster, and stand-alone SQL servers in our environment?

Thanks 


Thursday, July 26, 2012 - 4:35:57 AM - Arvind Back To Top

 

Hi Bru,

I am newbie  and tried to setup the email alert following above steps in sql server 2008

modified the email and profile name and it run successfully.

However, I am not getting mails.

can you please guide which step I am missing and what to do now.

I have setup mail alerts using sp_sqlNotify, some other stored procedure and able to get mails but not with the logs.

Thanks in advance.

 

 


Wednesday, March 14, 2012 - 8:00:58 PM - Sri Back To Top

Jason, sorry for late reply. Setting up alerts and operators was my first choice. I set up alerts from 16 - 25 , no luck , none alerted while testing. Are they practically working on prod boxes? Do they spam a lot by triggering mails. My requirement is to receive alerts when there is a incident (failure) or a crisis ahead. On side note I am writing and executing individual SPs based on issues like disk space , memory etc.

Thanks Bru for the help. While testing I dropped my test DB , below was the message alerted through mail. Can you suggest how to optimize to receive an exact message that particular DB has been dropped etc.

Mar 12 2012  5:48PM  SQL Server has encountered 2 occurrence(s) of cachestore flush for the 'Object Plans' cachestore (part of plan cache) due to some database maintenance or reconfigure operations.

Mar 12 2012  5:48PM  SQL Server has encountered 2 occurrence(s) of cachestore flush for the 'SQL Plans' cachestore (part of plan cache) due to some database maintenance or reconfigure operations.

Mar 12 2012  5:48PM  SQL Server has encountered 2 occurrence(s) of cachestore flush for the 'Bound Trees' cachestore (part of plan cache) due to some database maintenance or reconfigure operations.

 


Tuesday, March 13, 2012 - 9:54:16 AM - Jason Back To Top

Srinivas.  I would recommend setting up an operator and alerts on all your production sql servers.   For failed backups, Severity 16 is one that you want. 

 

Here's a sampe of creating the alert and the notification to an operator I've named "SqlDba"

 

USE [msdb]
GO

EXEC msdb.dbo.sp_add_alert @name=N'Severity 016',
  @message_id=0,
  @severity=16,
  @enabled=1,
  @delay_between_responses=60,
  @include_event_description_in=1,
  @job_id=N'00000000-0000-0000-0000-000000000000'
GO

EXEC msdb.dbo.sp_add_notification @alert_name=N'Severity 016', @operator_name=N'SQL DBA', @notification_method = 1
GO


Tuesday, March 13, 2012 - 9:08:55 AM - Bru Medishetty Back To Top

Hi Srinivas,

Here are some suggestions..

Choose a drive which has very least amount of free disk space and ensure you are creating backups (Full or Log ) in that drive, let the SQL Agent do its job of doing frequent backups and when the free space is not sufficient, your backup will fail and an entry is written in SQL Server Log..

OR

Change your database recovery model

OR

Try logging with a wrong password..

 


Monday, March 12, 2012 - 4:15:22 PM - Srinivas Back To Top

  • Transaction log being full causing a database backup failure
  • An error such as SQL Server has encountered x number of occurrence(s) of I/O requests taking longer than 15 seconds to complete
  • DBCC CHECKDB printed errors
  • and so on.

Can someone suggest how to test the above ?


Monday, March 12, 2012 - 3:41:03 PM - Srinivas Back To Top

I jus sorted it , sorry for spam.


Monday, March 12, 2012 - 3:28:35 PM - Srinivas Back To Top

Bru , Good Morning!!

Thanks for the article. I have got a query :-

It is throwing below error when i am trying to create alerting stored procedure.

 

Msg 119, Level 15, State 1, Procedure SQL_ErrorLog_Alert_Notify, Line 30

 

Must pass parameter number 4 and subsequent parameters as '@name = value'. After the form '@name = value' has been used, all subsequent parameters must be passed in the form '@name = value'.


Friday, March 11, 2011 - 10:51:33 AM - Bru Medishetty Back To Top

Greg,

May I suggest you to include the below code 

"OR ContinuationRow = 1"

at the end of the delete code for SQL Server 2000 (At the end of "DELETE FROM #ErrorLog2000 " code.

I know, this causes deletion of all rows which are continuation of the previous row. I would work on finding solution which

does not to delete the continuation rows and post a reply here...

 


Friday, March 11, 2011 - 6:43:38 AM - Greg Back To Top

Bru,

 

Firstly I like your approche and it's working fine on SQL 2005, but on SQL 2000

SQL_ErrorLog_Alert sp is failing with

 

Msg 241, Level 16, State 1, Line 1

Syntax error converting datetime from character string.

on

 

  WHERE (CAST(LEFT(LTRIM(ErrorLog),23) AS DATETIME) 

 

  < CAST(DATEADD(MI,-6,GETDATE()) AS VARCHAR(23)))

 

because it's trying to convert non date string to date on continuation rows.

For example:

 

2011-03-11 10:30:01.47 backup    Log backed up: Database: Database, creation date(time): 2006/08/22(17:06:32), first LSN: 7238:109:1, last LSN: 7238:122:1, number of dump devices: 1, device information: (FILE=1, TYPE=DISK: {'E:\Backup\Data

Base\Database_201103111030.TRN'}).

 

The first row with ContinuationRow = 0 has a date in first 23 characters and can be converted do date format but the second row with ContinuationRow = 1 doesn't and therefore will fail the convertion and the sp.

 

Any suggestions how to get it working on SQL 2000?

 

 

 

 

 

 

 


Wednesday, March 02, 2011 - 8:24:44 AM - Bru Medishetty Back To Top

Hi Mallikarjuna,

Good to know that your issue was solved. Thanks for your feedback...

Bru Medishetty.


Wednesday, March 02, 2011 - 4:15:05 AM - Arjun Back To Top

Hello Bru,

Thank you for your assistance. Actually i'm creating table along with first SP. But somehow table was not created.

So i have created the table separately. Now it's working.

Nice article...

Regards,

Karnatakapu Mallikarjuna


Thursday, February 24, 2011 - 9:34:22 AM - Bru Medishetty Back To Top

I wanted to clarify one more time, have you created a new database called AlertsDB in you system? if so can you make sure that table exists as SQL_ErrorLog..

If you have created the table / stored procedure in an existing database then replace that database name in place of AlertsDB.

Hope that solves the issue..

Regards,

Bru Medishetty


Thursday, February 24, 2011 - 12:39:00 AM - Arjun Back To Top

Hello Bru,

Yes. I'm using as it is. Still it's giving same error.

I did try to debug it but still same error at line 54 in second SP.

SET @SUBJECT = @@SERVERNAME + ' SQL-SERVER ERROR LOG SUMMARY'
  DECLARE CURSOR1 CURSOR
  FOR SELECT [TEXT] FROM AlertsDB.DBO.SQL_ErrorLog    ------ ERROR giving statement.

Regards

Arjun

 


Wednesday, February 23, 2011 - 8:56:41 AM - David Bird Back To Top

Nice article, I liked how you added links to mssqtips for items you referenced in your article. That is very helpful.

If the linked server has permissions to drop/create the stored procedure you might want to recreate it every time you call it. I found every so often I am changing the criteria I use when identifying the errors I wish to be emailed about. Recreating it, ensures every server is using the latest version.


Wednesday, February 23, 2011 - 7:46:21 AM - Bru Medishetty Back To Top

Hi Arjun,

 

Have you created the stored procedure in a database called "AlertsDB"? Here I am using a database named AlertsDB in my example. I also mentioned in the code with suggestions to use the database you are using...

 

Please let me know if that solves...


Wednesday, February 23, 2011 - 6:39:06 AM - Arjun Back To Top

Hello Bru,

Thank you for sharing this nice article.

I have tried using this. But it's giving me the following error. I did verified & followed all the steps. Still it's not resolving.

Msg 208, Level 16, State 1, Procedure SQL_ErrorLog_Alert_Notify, Line 54

Invalid object name 'AlertsDB.DBO.SQL_ErrorLog'.

 

Regards,

Arjun

 


Tuesday, February 22, 2011 - 9:39:47 AM - Bru Medishetty Back To Top

Thanks for the recommendations Alan..


Tuesday, February 22, 2011 - 9:38:24 AM - Bru Medishetty Back To Top

Jason, thanks for the comments. 

Either way I will need to be filtering the data, so I ended up cleaning the temp table and pull everything from it.

About the default paramaters, yes I could defintely do that.

Regarding the job failure, we actually have Alerts set up through Microsoft System Center Operations Manager (SCOM).


Tuesday, February 22, 2011 - 12:18:35 AM - Alan Back To Top

Nice aproach - I like it.  A lot of folks recycle the error logs daily so I would also add a scan of the previos error log to the proc in case the search spans more than 1 log.


Monday, February 21, 2011 - 2:15:42 PM - Jason Back To Top

Interesting approach.  I have to wonder why you populate the table, then delete the unrelated data, then query the remaining data, and finally drop the table.  Why not populate the table, and then query out only the data you want via the where clause NOT IN logic, and then drop the table?

 

Another thing you can do is set default values for your parameters, and if the user then passes in a value for that parameter, it will override the default value.

CREATE PROCEDURE [dbo].[SQL_ErrorLog_Alert]
@Mins int = 6
Would get rid of this logic

IF @Minutes IS NULL  -- If the optional parameter is not passed, @Mins value is set to 6
SET @Mins = 6
ELSE
SET @Mins = @Minutes

I also would recommend some notification of job failure, so that you know that a quiet period of no email alerts from your system is truely caused by no errors in the log files.


Learn more about SQL Server tools