As SQL Server administrators, we might want to know what is happening on the server during the day and if any unwanted incidents are occurring. This is where Alerts come into play. If an event occurs that is monitored by a defined alert, a notification will be sent via email to the operator who can take action or be aware of the situation.

Alerts can be set up for virtually anything, including condition scripting through PowerShell. In any case, it’s a good thing to have. Alerts are formally managed by SQL Server Agent.

What Do We Need to Have Set Up for Alerts on SQL Server?

(1) Notification delivery is done through database mail, so the first requirement is to set up email. You can find how to set up email in the article here. If you haven’t done so, please do and come back later.

(2) The next step is defining an operator, meaning the recipient of notifications in case of an event occurrence.

(3) The last requirement is enabling Alerts in SQL Server Agent and configuring the email profile through which notifications will be sent to the operator.

Creating an Alert Triggered by the Event “Someone Divides by 0”

To lighten this serious situation, I will create an alert for when someone attempts to divide by zero. You will naturally use alerts to monitor more critical events, such as fatal errors, server resource shortages, and so on.

(1) Right-click on Alerts -> New Alert.

• Fill in the alert name
• Alert type – you can choose from several options – SQL event (in this case), performance condition, WMI Event
• Select the database for which the alert should apply
• Next, choose the condition that will trigger the SQL Server alert

(2) In the menu, select Response and configure that you want to send a notification to the selected operator via email. The Alert setup is complete.

(3) Raise a custom error (or information – severity 9). The error must be raised WITH LOG; otherwise, the Alert won’t be sent.

BEGIN TRY
SELECT 1/0
END TRY
BEGIN CATCH
RAISEERROR(‘Division by zero is not allowed……’,9,1) WITH LOG
END CATCH

After executing the script, you will see information that division by zero is not allowed, and you should receive an email…

… and it has been delivered.