How to create an alert based on a custom error message.

Problem: How to create an alert based on a custom error message.

Solution: sp_add_alert

Alerts are useful to notify when a job fails or an error occurs based on an error number or error message. This can be helpful for monitoring of the database disk errors as well.

Example:

In this article I am going to show you we can create Alerts.

Example 1: I this example, I would like to add an alert to check for a specific error message id.

EXEC msdb.dbo.sp_add_alert 

    @name = N'Maintenance Alert', 

       @message_id = 50000,  

       @enabled=1, --enable (1)/disable(0)

       @delay_between_responses=60, --number of seconds between emails

       @include_event_description_in=5, --Email with error log

       @severity = 16,  --user defined

       @notification_message = N'Please contact Tech Support as soon as possible',  

       @job_name = N'’;

In the below picture you can see under the SQL Server Agent there is an alert which has been just created.

Inside the alert window we can see the information we passed into the system procedure as below:

Example 2: In this example let’s see how we can update the alert, to disable the alert.

EXEC msdb.dbo.sp_update_alert 

                @name = N'Maintenance Alert', 

@enabled=0

As per the below picture the icon on the alerts has red arrow with downwards pointed which indicates the alert has been disabled.

In the next article let’s see how we can notify an operator using alerts that are sent.