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.

SQL Alerts - Where to Find Them

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.

SQL Alerts - Configuration in SQL Server Agent

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

New Alert - Someone Divides by 0

(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.

SQL Alert - Response

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

RAISEERROR(‘Division by zero is not allowed……’,9,1) WITH LOG

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

SQL Alert - Raising Error and Waiting for Email

… and it has been delivered.

SQL Alert - Email Has Been Delivered

Rate this post

Ing. Jan Zedníček - Data Engineer & Controlling

My name is Jan Zedníček and I have been working as a freelancer for many companies for more than 10 years. I used to work as a financial controller, analyst and manager at many different companies in field of banking and manufacturing. When I am not at work, I like playing volleyball, chess, doing a workout in the gym.

🔥 If you found this article helpful, please share it or mention me on your website

Leave a Reply

Your email address will not be published. Required fields are marked *