In this blog I want to introduce to you a tool you can use directly from Microsoft SQL Server.  The tool I am talking about is an Alert.  The function of an Alert is to notify you when a particular condition has occurred.  This is similar to when running a scheduled SQL Server Agent job, except in the case of an Alert, it is based on condition instead of on a schedule. I will show you how to setup an Alert for a specific error.  In the next blog, part two, I will show you how to set an alert to fire when a blocking situation occurs in SQL Server. 

As pre-requisite, Alerts need to have SQL Server Agent and Database Mail setup and running.  SQL Server Agent drives the Alert tool and if it's not running, you will not receive the alert email.  The SQL Server engine uses Database Mail component to send emails. Database Mail uses SMTP(Simple Mail Transfer Protocol) to deliver emails to recipients.  Note, I will not be describing the setup for SQL Server Agent nor Database Mail.

There are three different types of alerts that can be set up to monitor:

SQL Server Event Alert - This type of alert fires when a SQL Server event occurs. The events that can be selected will be displayed from the dropdown list. 

SQL Server performance condition alert - This type of alert is when a performance counter value is met.  For example, when a specific database size reaches 100,000,000 KB (100 gig)

WMI Event Alert -  (Windows Management Instrumentation) This alert type occurs when a new file appears in a specific folder. 

After Database Mail and SQL Server Agent are configured, here are steps to create and configure Alerts.  First, a reminder to always to try these steps on a TEST environment first before you attempt in production.  Next, from SQL Server Management Studio, locate the Alerts folder under SQL Server Agent. Then, right click Alerts and select New Alert...

Let's create an alert for condition of Insufficient Resources for SQL Server. From the new alert window, select the following options. (You would replace the Database name with the X3 database) 

Next click on the Response page and add yourself as the Operator. Check Notify operators and place a check-mark in the E-mail column next to your operator account:

And finally, click on the Options page. You don't really have to add anything to this page, but you could add a more personal message in the Additional notification message to send box. I added the following:

After you click OK, you should see your new alert in the Alerts list:

..

Now, let's test the Error 17 Alert. We need to create the condition to make the alert fire. I am going to fake this one, but it will give you an idea of how it works. From your SQL Server environment, and open a new Query window.  In the query window, switch the database context to the Database name(mine was the AdventureWorks db)  entered and type the following command:

RAISERROR ('Test Error', 17, 1) WITH LOG 

Highlight the RAISERROR line just entered and execute it.  After a few moments, you will receive an email similar to the one below:

Every time 'Error 17' is encountered within the database specified, the recipient(s) will receive a notification email.   There a lot of different notifications you can choose to setup as an Alert. You will probably find there are several types of Alerts that can be setup to notify you when they occur.    If you have set up or used SQL Server Alerts before, let me know which ones have helped you and what you experienced.