1: -- Add important SQL Agent Alerts to your instance
2:
3: -- Change the Alert names and @OperatorName as needed
4: -- Glenn Berry
5: -- SQLskills
6: -- 9-27-2012
7:
8: USE [msdb];
9: GO
10:
11: -- Make sure you have an Agent Operator defined
12: -- Change @OperatorName as needed
13: DECLARE @OperatorName sysname = N'SQLDBAs';
14:
15: -- Change @CategoryName as needed
16: DECLARE @CategoryName sysname = N'SQL Server Agent Alerts';
17:
18: -- Add Alert Category if it does not exist
19: IF NOT EXISTS (SELECT *
20: FROM msdb.dbo.syscategories
21: WHERE category_class = 2 -- ALERT
22: AND category_type = 3
23: AND name = @CategoryName)
24: BEGIN
25: EXEC dbo.sp_add_category @class = N'ALERT', @type = N'NONE', @name = @CategoryName;
26: END
27:
28: -- Get the server name
29: DECLARE @ServerName sysname = (SELECT @@SERVERNAME);
30:
31:
32: -- Alert Names start with the name of the server
33: DECLARE @Sev19AlertName sysname = @ServerName + N' Alert - Sev 19 Error: Fatal Error in Resource';
34: DECLARE @Sev20AlertName sysname = @ServerName + N' Alert - Sev 20 Error: Fatal Error in Current Process';
35: DECLARE @Sev21AlertName sysname = @ServerName + N' Alert - Sev 21 Error: Fatal Error in Database Process';
36: DECLARE @Sev22AlertName sysname = @ServerName + N' Alert - Sev 22 Error Fatal Error: Table Integrity Suspect';
37: DECLARE @Sev23AlertName sysname = @ServerName + N' Alert - Sev 23 Error: Fatal Error Database Integrity Suspect';
38: DECLARE @Sev24AlertName sysname = @ServerName + N' Alert - Sev 24 Error: Fatal Hardware Error';
39: DECLARE @Sev25AlertName sysname = @ServerName + N' Alert - Sev 25 Error: Fatal Error';
40: DECLARE @Error825AlertName sysname = @ServerName + N' Alert - Error 825: Read-Retry Required';
41:
42:
43:
44: -- Sev 19 Error: Fatal Error in Resource
45: IF NOT EXISTS (SELECT name FROM msdb.dbo.sysalerts WHERE name = @Sev19AlertName)
46: EXEC msdb.dbo.sp_add_alert @name = @Sev19AlertName,
47: @message_id=0, @severity=19, @enabled=1,
48: @delay_between_responses=900, @include_event_description_in=1,
49: @category_name = @CategoryName,
50: @job_id=N'00000000-0000-0000-0000-000000000000';
51:
52: -- Add a notification if it does not exist
53: IF NOT EXISTS(SELECT *
54: FROM dbo.sysalerts AS sa
55: INNER JOIN dbo.sysnotifications AS sn
56: ON sa.id = sn.alert_id
57: WHERE sa.name = @Sev19AlertName)
58: BEGIN
59: EXEC msdb.dbo.sp_add_notification @alert_name = @Sev19AlertName, @operator_name=@OperatorName, @notification_method = 1;
60: END
61:
62:
63:
64:
65:
66: -- Sev 20 Error: Fatal Error in Current Process
67: IF NOT EXISTS (SELECT name FROM msdb.dbo.sysalerts WHERE name = @Sev20AlertName)
68: EXEC msdb.dbo.sp_add_alert @name = @Sev20AlertName,
69: @message_id=0, @severity=20, @enabled=1,
70: @delay_between_responses=900, @include_event_description_in=1,
71: @category_name = @CategoryName,
72: @job_id=N'00000000-0000-0000-0000-000000000000'
73:
74: -- Add a notification if it does not exist
75: IF NOT EXISTS(SELECT *
76: FROM dbo.sysalerts AS sa
77: INNER JOIN dbo.sysnotifications AS sn
78: ON sa.id = sn.alert_id
79: WHERE sa.name = @Sev20AlertName)
80: BEGIN
81: EXEC msdb.dbo.sp_add_notification @alert_name = @Sev20AlertName, @operator_name=@OperatorName, @notification_method = 1;
82: END
83:
84:
85:
86: -- Sev 21 Error: Fatal Error in Database Process
87: IF NOT EXISTS (SELECT name FROM msdb.dbo.sysalerts WHERE name = @Sev21AlertName)
88: EXEC msdb.dbo.sp_add_alert @name = @Sev21AlertName,
89: @message_id=0, @severity=21, @enabled=1,
90: @delay_between_responses=900, @include_event_description_in=1,
91: @category_name = @CategoryName,
92: @job_id=N'00000000-0000-0000-0000-000000000000';
93:
94: -- Add a notification if it does not exist
95: IF NOT EXISTS(SELECT *
96: FROM dbo.sysalerts AS sa
97: INNER JOIN dbo.sysnotifications AS sn
98: ON sa.id = sn.alert_id
99: WHERE sa.name = @Sev21AlertName)
100: BEGIN
101: EXEC msdb.dbo.sp_add_notification @alert_name = @Sev21AlertName, @operator_name=@OperatorName, @notification_method = 1;
102: END
103:
104:
105:
106: -- Sev 22 Error: Fatal Error Table Integrity Suspect
107: IF NOT EXISTS (SELECT name FROM msdb.dbo.sysalerts WHERE name = @Sev22AlertName)
108: EXEC msdb.dbo.sp_add_alert @name = @Sev22AlertName,
109: @message_id=0, @severity=22, @enabled=1,
110: @delay_between_responses=900, @include_event_description_in=1,
111: @category_name = @CategoryName,
112: @job_id=N'00000000-0000-0000-0000-000000000000';
113:
114: -- Add a notification if it does not exist
115: IF NOT EXISTS(SELECT *
116: FROM dbo.sysalerts AS sa
117: INNER JOIN dbo.sysnotifications AS sn
118: ON sa.id = sn.alert_id
119: WHERE sa.name = @Sev22AlertName)
120: BEGIN
121: EXEC msdb.dbo.sp_add_notification @alert_name = @Sev22AlertName, @operator_name=@OperatorName, @notification_method = 1;
122: END
123:
124:
125: -- Sev 23 Error: Fatal Error Database Integrity Suspect
126: IF NOT EXISTS (SELECT name FROM msdb.dbo.sysalerts WHERE name = @Sev23AlertName)
127: EXEC msdb.dbo.sp_add_alert @name = @Sev23AlertName,
128: @message_id=0, @severity=23, @enabled=1,
129: @delay_between_responses=900, @include_event_description_in=1,
130: @category_name = @CategoryName,
131: @job_id=N'00000000-0000-0000-0000-000000000000';
132:
133: -- Add a notification if it does not exist
134: IF NOT EXISTS(SELECT *
135: FROM dbo.sysalerts AS sa
136: INNER JOIN dbo.sysnotifications AS sn
137: ON sa.id = sn.alert_id
138: WHERE sa.name = @Sev23AlertName)
139: BEGIN
140: EXEC msdb.dbo.sp_add_notification @alert_name = @Sev23AlertName, @operator_name = @OperatorName, @notification_method = 1;
141: END
142:
143:
144: -- Sev 24 Error: Fatal Hardware Error
145: IF NOT EXISTS (SELECT name FROM msdb.dbo.sysalerts WHERE name = @Sev24AlertName)
146: EXEC msdb.dbo.sp_add_alert @name = @Sev24AlertName,
147: @message_id=0, @severity=24, @enabled=1,
148: @delay_between_responses=900, @include_event_description_in=1,
149: @category_name = @CategoryName,
150: @job_id=N'00000000-0000-0000-0000-000000000000';
151:
152: -- Add a notification if it does not exist
153: IF NOT EXISTS(SELECT *
154: FROM dbo.sysalerts AS sa
155: INNER JOIN dbo.sysnotifications AS sn
156: ON sa.id = sn.alert_id
157: WHERE sa.name = @Sev24AlertName)
158: BEGIN
159: EXEC msdb.dbo.sp_add_notification @alert_name = @Sev24AlertName, @operator_name = @OperatorName, @notification_method = 1;
160: END
161:
162:
163: -- Sev 25 Error: Fatal Error
164: IF NOT EXISTS (SELECT name FROM msdb.dbo.sysalerts WHERE name = @Sev25AlertName)
165: EXEC msdb.dbo.sp_add_alert @name = @Sev25AlertName,
166: @message_id=0, @severity=25, @enabled=1,
167: @delay_between_responses=900, @include_event_description_in=1,
168: @category_name = @CategoryName,
169: @job_id=N'00000000-0000-0000-0000-000000000000';
170:
171: -- Add a notification if it does not exist
172: IF NOT EXISTS(SELECT *
173: FROM dbo.sysalerts AS sa
174: INNER JOIN dbo.sysnotifications AS sn
175: ON sa.id = sn.alert_id
176: WHERE sa.name = @Sev25AlertName)
177: BEGIN
178: EXEC msdb.dbo.sp_add_notification @alert_name = @Sev25AlertName, @operator_name = @OperatorName, @notification_method = 1;
179: END
180:
181:
182:
183: -- Error 825: Read-Retry Required
184: IF NOT EXISTS (SELECT name FROM msdb.dbo.sysalerts WHERE name = @Error825AlertName)
185: EXEC msdb.dbo.sp_add_alert @name = @Error825AlertName,
186: @message_id=825, @severity=0, @enabled=1,
187: @delay_between_responses=900, @include_event_description_in=1,
188: @category_name = @CategoryName,
189: @job_id=N'00000000-0000-0000-0000-000000000000';
190:
191:
192: -- Add a notification if it does not exist
193: IF NOT EXISTS(SELECT *
194: FROM dbo.sysalerts AS sa
195: INNER JOIN dbo.sysnotifications AS sn
196: ON sa.id = sn.alert_id
197: WHERE sa.name = @Error825AlertName)
198: BEGIN
199: EXEC msdb.dbo.sp_add_notification @alert_name = @Error825AlertName, @operator_name = @OperatorName, @notification_method = 1;
200: END
201: GO
202:
203:
204:
205:
206: