This article assumes that you configured both database mail and system operators. Please see my previous articles if you have not done these tasks already.
Today, I am going to talk about setting up alerts for system error messages that might occur with your SQL server database. Since alerts have been around for a long time, most of this information is backward compatible with older software versions.
The database engine defines the importance of an error messages with a severity level.
Low importance errors are defined by levels 0 to 10 (informational only); medium importance errors are defined by levels 11 to 16 (user/process issues); and high importance errors are defined by levels 17 to 25 (critical database issues).
Every good database administrator should have alerting on high importance error messages. Optional alerting can be enabled on medium importance error messages if you have time to research possible issues.
The code below uses sp_add_category to create a category for these alerts, sp_add_alert to add a new alert based upon severity, and sp_add_notification to ensure a email goes out to the operator when the alert happens. A simple WHILE construct is used to setup alerts for errors with severity ranging from 17 to 25.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 |
-- -- Add alerts for severity errors 17 to 25 -- -- Add a custom category for alerts EXEC msdb.dbo.sp_add_category @class=N'ALERT', @type=N'NONE', @name=N'[Alert By Severity]' ; GO -- Loop from 17 to 26 DECLARE @VAR_ACNT INT; DECLARE @VAR_ANAME VARCHAR(125); SET @VAR_ACNT = 17; -- Add severe alerts to system WHILE (@VAR_ACNT < 26) BEGIN SET @VAR_ANAME = 'Alert For Severity ' + STR(@VAR_ACNT, 2, 0); -- Add alert to system EXEC msdb.dbo.sp_add_alert @name=@VAR_ANAME, @message_id=0, @severity=@VAR_ACNT, @enabled=1, @delay_between_responses=60, @include_event_description_in=5, @category_name=N'[Alert By Severity]', @job_id=N'00000000-0000-0000-0000-000000000000'; -- Attach the operator to the alerts EXEC msdb.dbo.sp_add_notification @alert_name = @VAR_ANAME, @operator_name = N'John Miner', @notification_method = 1 ; PRINT 'Add: ' + @VAR_ANAME; SET @VAR_ACNT = @VAR_ACNT + 1; END |
The sp_help_alert stored procedure will return all alerts that are defined in the system. These alerts are stored in the msdb.dbo.sysalerts table.
|
1 2 3 |
-- Show the current alerts EXEC msdb.dbo.sp_help_alert GO |
The sp_help_notification stored procedure used below shows all the alerts in which I am the notified operator.
|
1 2 3 4 5 6 7 |
-- Show the notification EXEC msdb.dbo.sp_help_notification @object_type = N'ALERTS', @name = N'John Miner', @enum_type = N'ACTUAL', @notification_method = 7 ; GO |
Like most system objects, alerting can be enabled or disabled. The sp_update_alert allows me to disable the ‘Alert For Severity 25’ alert.
|
1 2 3 4 5 |
-- Disable Alert 25 EXEC msdb.dbo.sp_update_alert @name = N'Alert For Severity 25', @enabled = 0 ; GO< |
Last but not least, SQL Server supplies the sp_delete_alert stored procedure to remove the alerts. The code below uses the WHILE construct to remove all alerts we just defined.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 |
-- Loop from 17 to 26 DECLARE @VAR_DCNT INT; DECLARE @VAR_DNAME VARCHAR(125); SET @VAR_DCNT = 17; -- Remove the alerts WHILE (@VAR_DCNT < 26) BEGIN SET @VAR_DNAME = 'Alert For Severity ' + STR(@VAR_DCNT, 2, 0) EXEC msdb.dbo.sp_delete_alert @VAR_DNAME; PRINT 'Delete: ' + @VAR_DNAME SET @VAR_DCNT = @VAR_DCNT + 1 END |
In summary, I talked about adding alerting for high importance errors. Each alert needs to be associated with an operator to be notified. Optional alerting can be done on medium importance errors. One feature that I did not go over is the @job parameter which allows a DBA to define a job to execute if an alert is fired. I will continue next time by talking about setting performance alerts which depends upon WMI queries to monitor operating system counters such as memmory and CPU.
Appreciate it for all your efforts that you have put in this. Very interesting information. “He who despairs over an event is a coward, but he who holds hope for the human condition is a fool.” by Albert Camus.