Use case #
You need to create email alerts for any events triggered in an SQL Server instance. Follow the steps below to configure and test email alerts in SQL Server.
Setup SQL Server Database Mail #
First setup database mail with a database mail profile. Open the SQL instance in SQL Server Management Studio and navigate to Management --> Database Mail, right click and choose to "Configure Database Mail".
Define a SQL Server Operator #
Connect to the SQL Server instance using Microsoft SQL Management Studio. Expand the SQL Server Agent tree and right click on Operators and select New Operator.
Specify an operator Name, E-mail name, and click OK
Setup SQL Server Agent Settings #
Right click SQL Server Agent and select Properties.
Select Alert System in the left pane and do the following:
- Check Enable mail profile
- Verify Mail system is Database Mail
- Verify Mail profile is SQLAlerts that we discussed above
- Check Include body of e-mail in the notification message
- Click OKto save settings.
Restart SQL Server Agent to Activate Settings #
Warning: Restarting SQL Server Agent will cancel any executing jobs, so this should be done when there is are no active jobs. This does not affect the corresponding SQL Server process, only the SQL Server Agent process.
Define a SQL Server Alert #
Inside SQL Server Management Studio, expand the SQL Server Agent tree and right click on Alerts. Select New Alert.
You have the following alert types as available options in the General tab:
- SQL Server event alert
- SQL Server performance condition alert
- WMI event alert
You can also check the "raise alert when message contains" field in order to raise an alert based on specific words inside the SQL error logs.
Afterwards click on the Response pane:
- Check Notify operators
- Check E-mail for the operator
Click on the Options pane:
- For Include alert error text in select E-mail
- For Delay between responses enter 2 minutes
- Click OK
Verify SQL Server Alert is Working #
The operator should receive an e-mail. Open the Alert we just setup and click on the History page. The fields should be updated when the alert is triggered as shown below.
Disable the SQL Server Alert Test #
To disable the SQL Server alert you can either go to the General page for the alert and remove the checkmark from Enable and click OK or alternatively you can right click on the Alert name and select Disable.
