Setup SQL Server alerts and email operator notifications

To totally unlock this section you need to Log-in


Login

Use the Notify Operators Task dialog to add an automatic notification to this maintenance plan. To use this task you must have Database Mail enabled and properly configured with MSDB as a Mail Host Database, and have a Microsoft SQL Server Agent operator with a valid e-mail address.

This task uses the sp_notify_operator stored procedure.

Options

  • Connection: select the server connection to use when performing this task.
  • New: create a new server connection to use when performing this task. The New Connection dialog box is described below.
  • Operators to notify: specify the recipient of the e-mail.
  • Notification message subject: specify the text to include in the notification message subject line.
  • Notification message body: specify the text to include in the notification message body.
  • View T-SQL: view the Transact-SQL statements performed against the server for this task, based on the selected options.

Problem

Setting up an alert to e-mail an operator with a message is a multiple step process. You can setup database mail, define an operator and an alert, but still no e-mail is being sent. So how do you send an alert to an operator?

Solution

Note: This solution involves restarting SQL Agent.

Database Mail

First setup database mail with a profile named SQLAlerts. The profile can be named anything but in these instructions, the profile name SQLAlerts is referenced. If you wish to use a different profile name just substitute accordingly.

Define Operator

  • Connect to the instance using Microsoft SQL Management Studio.
  • Double-Click SQL Server Agent.
  • Right Click on Operators and select New Operator.

Setup SQL Server alerts and email operator notifications

Specify an operator Name, E-mail name, and click OK.

Setup SQL Server alerts and email operator notifications

Setup SQL Agent Settings

Note: This step is often overlooked when creating an alert for the first time. SQL Agent must be setup correctly for operators to receive an alert e-mail.

Right Click SQL Server Agent > select Properties:

Setup SQL Server alerts and email operator notifications

Select Alert System in the left pane:

  • Checkmark > Enable mail profile
  • Verify Mail system: Database Mail
  • Verify Mail Profile: SQLAlerts
  • Checkmark > Include body of e-mail in the notification message.
  • Click OK.

Setup SQL Server alerts and email operator notifications

Restart SQL Agent to activate settings.

Warning: Restarting SQL Agent will cancel any executing jobs.

Setup SQL Server alerts and email operator notifications

Define Alert

This sample alert will send an email when TEMPDB database gets larger than 0 KB. This setting is set zero so the alert can be tested. Once verified, you will need to update its settings to a reasonable amount or disable it.

Right-Click Alerts and select New Alert:

Setup SQL Server alerts and email operator notifications

On the General pane specify:

  • Name: TEMPDB Growing
  • Type: SQL Server performance condition alert
  • Object: SQLServer:Databases
  • Counter: Data File(s) (KB)
  • Instance: tempdb
  • Alert if counter: rises above
  • Value: 0

Setup SQL Server alerts and email operator notifications

Click Response in left pane:

  • Checkmark > Notify operators
  • Checkmark > E-mail for the operator

Setup SQL Server alerts and email operator notifications

Click Options in left pane:

  • Checkmark > Include alert error text in E-mail
  • Delay between response: 2 minutes

Click OK.

Setup SQL Server alerts and email operator notifications

Verify Alert is Working

The operator should receive an e-mail if not see the troubleshooting section. Open the Alert. Click History in the left pane. The fields are updated when the alert is triggered.

Setup SQL Server alerts and email operator notifications

Stop the Test

To stop the Alert from being sent every two minutes, you have two options disable the alert or provide a more appropriate size and response times.

To disable alert

Click General in left pane and remove the checkmark from Enable and click OK or right-click on Alert name and select Disable.

Setup SQL Server alerts and email operator notifications

To change the settings

  • Click General in left pane.
  • Change value to value larger than existing TEMPDB database file save. The current file size is included in the e-mailed alert.
  • Click Options in left pane.
  • Change response to 720 minutes which is 12 hours.
  • Click OK.

Setup SQL Server alerts and email operator notifications

Setup SQL Server alerts and email operator notifications

Troubleshooting

If the operator does not receive an e-mail:

Test database mail by sending a test e-mail to the operator’s e-mail address and make sure you restarted SQL Agent and it is running.

Category