Must Set SQL Server Alerts

According to Webster dictionary, Alert is defined as the act of notifying potential danger or risk before it happens. You can create a database alert to be raised when a threshold value for a system monitor counter is reached. This is done to prevent the database from major failure and fix it as soon as the problem started to appear. SQL Server alert is one way to get a head start on trouble before it brings the system down. 

Notification is the act of sending the detail on the problem to DBA via email, logs, pager, or whatever medium you choose. The database mail must be set and the operator must be configured before you set the alerts. Without database mail and operator, the notification can't function.

Alerts can be raised where there are user errors, software or hardware failure. The alerts raised by user error can be correct and therefore you are not required to send a notification for these types of alert to DBA team. DBA are busy and don't have time for user errors.

User Alerts: The severity level 10; messages are informational and indicate problem cause by the mistakes in the information user entered. Severity levels from 11 through 16 are generated by the user and can be corrected by the users. Therefore it is not required to set a notification for these kinds of errors.

Must Set Alerts:

Software & Hardware Alerts: The listed below alerts should be set on every SQL Databases, no exception.

1. Severity Level 17: Out of Resources or exceeding System defined limit
2. Severity Level 18: Non Fatal Software error
3. Severity Level 19: Non-Configurable Internal limit has been exceeded.
4. Severity Level 20: Current Statement has encountered problem
5. Severity Level 21:  Encountered problem which affects all processes in database
6. Severity Level 22: Indicates the database objects is corrupt or damaged
7. Severity Level 23: Problem with database integrity.
8. Severity Level 24:  Encountered Disk failure or other hardware failure
9. Severity Level 25:  System Error

Severity Level 17-19: System/Database Administrator should be informed for these errors. These are software error which the database user cannot correct.
Severity Level 20-24:  These are fatal errors. The connections are terminated and may not reconnect. These seventies are also written to the error log file.

I encourage everyone to explore  master.dbo.sysmessages view for all the SQL Server related error messages. It stores all the error, severity, dlevel, and descriptions. With this, you can count the total numbers of SQL Server errors.

SQL Syntax:

select *
from master.dbo.sysmessages;

How many SQL Server errors are there?

How do you set alerts? There are two ways one can set up alerts and notification. SSMS is a quick and easy way of adding alerts. There are stored procedures options which lets you set up alert. This requires you to know all the parameter that goes to procedures. I recommend using SSMS to beginner or anyone. Get your alerts set if not done already.