Automating Alerts
SQL Server Agent provides a great mechanism for sending alerts via email in response to various events. Once you’ve setup Database Mail, and configured a SQL Server Agent Operator, you should add alerts for severe errors that affect the health of your SQL Server. Creating Alerts can be tedious, but automating Alerts is simple, with the easy code below that automates creating alerts in response to critical events. Automating alerts is important because it provides a standardized Alert configuration that can be used by all the SQL Servers in your organization.
A Party Angling, by George Morland
|
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 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 |
USE msdb; DECLARE @dba_operator sysname = N'DBA'; IF NOT EXISTS ( SELECT 1 FROM msdb.dbo.sysoperators so WHERE so.name = @dba_operator ) BEGIN DECLARE @msg nvarchar(1000); SET @msg = N'Could not locate the ' + @dba_operator + N' operator!'; RAISERROR (@msg, 10, 1) WITH NOWAIT; END ELSE BEGIN IF NOT EXISTS ( SELECT 1 FROM dbo.syscategories sc WHERE sc.name = N'Alerts' ) BEGIN EXEC msdb.dbo.sp_add_category @class = 'ALERT', @type = 'NONE', @name = N'Alerts'; END; DECLARE @cmd nvarchar(max); SET @cmd = N''; SELECT @cmd = @cmd + CASE WHEN @cmd = N'' THEN N'' ELSE N' ' END + N' EXEC @return_code = msdb.dbo.sp_add_alert @name = N''Severity ' + CONVERT(nvarchar(10), sm.severity) + N' Error'' , @message_id = 0 , @severity = ' + CONVERT(nvarchar(10), sm.severity) + N' , @enabled = 1 , @delay_between_responses = 60 , @include_event_description_in = 1 --email , @category_name = N''Alerts'' , @job_id = NULL , @notification_message = N'' SERVER: ' + @@SERVERNAME + '''; IF @return_code <> 0 BEGIN PRINT N''Failed adding alert for "Severity ' + CONVERT(nvarchar(10), sm.severity) + N' Error"''; GOTO script_failure; END ELSE BEGIN PRINT N''Added alert "Severity ' + CONVERT(nvarchar(10), sm.severity) + N' Error"''; END EXEC @return_code = msdb.dbo.sp_add_notification @alert_name = N''Severity ' + CONVERT(nvarchar(10), sm.severity) + N' Error'' , @operator_name = N''DBA'' , @notification_method = 1; IF @return_code <> 0 BEGIN PRINT N''Failed adding notification to "Severity ' + CONVERT(nvarchar(10), sm.severity) + N' Error" alert!''; GOTO script_failure; END ELSE BEGIN PRINT N''Added DBA notification to "Severity ' + CONVERT(nvarchar(10), sm.severity) + N' Error" alert.''; END ' FROM sys.messages sm WHERE sm.language_id = 1033 AND sm.is_event_logged = 1 AND sm.severity IN (17, 18, 19, 20, 21, 22, 23, 24, 25) AND NOT EXISTS (SELECT 1 FROM dbo.sysalerts sa WHERE sa.name = N'Severity ' + CONVERT(nvarchar(10), sm.severity) + N' Error') GROUP BY sm.severity ORDER BY sm.severity; SET @cmd = N'DECLARE @return_code int = -1; BEGIN TRANSACTION; ' + @cmd + N' COMMIT TRANSACTION; GOTO end_of_script; script_failure: IF @@TRANCOUNT > 0 ROLLBACK TRANSACTION; end_of_script: '; EXEC sys.sp_executesql @cmd; END |
In current versions of SQL Server, the following alerts are automatically created for severity levels 17 through 25 that have at least a single error that will be logged to the event log. Errors that are not logged to the event log do not raise Alerts via SQL Server Agent.
The code uses the built-in functionality for adding alerts, sp_add_alert.
Check out the rest of our posts on SQL Server Agent!
[…] Hannah Vernon shows how you can use the SQL Server Agent to send automated e-mails on alerts: […]