SQL Server Wave

A business critical SQL Server job failed during the weekend without anyone knowing about it. When some errors are not predictable, a good system should at least send notifications when things go wrong. In this case, it would have been appropriate if an email was sent to the relevant person with as much details possible about the error.

In SQL Server, we need to setup the following two features:

1. Firstly, Database Mail; it should be enabled and configured. This has been covered in detail here.
2. Secondly, an Operator that will be used by SQL Agent jobs to notify the relevant person or group.

Let’s create a new Operator:

Right click on Operators and click New Operator...

In the next step, all we need is specify the email address of the person or group to receive notifications

Specify an email address

Now that the Operator is ready,  any sql agent job can use it

Job property - notification tab

Last but not least, let’s make sure that SQL Server Agent is able to use the Database Mail profile

SQL Server Agent properties

Alert System - choose the mail profile to use for sending notifications

Restart SQL Server Agent

That’s it.

2 Responses to “Setting up email notifications in SQL Server 2005/2008”