Thursday, November 7, 2019

What SQL Server Agent Alerts Do I have setup?

I am in a situation where I have to incorporate SQL Server Agent alerts in my monitoring and alerting strategy.

I needed a query (DMV) to get details on what alerts are setup on each server. And the result is the following query that I will run as a multi-server query.

SELECT a.[id]                        [alert_id], 
       a.[name]                      [alert_name], 
       a.[enabled]                   [is_alert_enabled], 
       o.[enabled]                   [is_operator_enabled], 
       o.[email_address]             [email_address], 
       o.[pager_address]             [pager_address], 
       o.[netsend_address]           [netsend_address],
       j.[name]                      [job_name], 
       a.[event_source]              [alert_event_source], 
       a.[event_category_id]         [alert_event_category_id], 
       sc.[name]                     [alert_category_name], 
       CASE sc.[category_class] 
         WHEN 1 THEN 'JOB' 
         WHEN 2 THEN 'ALERT' 
         WHEN 3 THEN 'OPERATOR' 
         ELSE '0' 
       END                           [alert_class_name], 
       sm.[description]              [alert_message_description],
       a.[event_id]                  [alert_event_id], 
       a.[message_id]                [alert_message_id], 
       a.[severity]                  [alert_severity], 
       a.[enabled]                   [alert_enabled], 
       a.[delay_between_responses]   [alert_delay_between_responses], 
       a.[last_occurrence_date]      [alert_last_occurrence_date], 
       a.[last_occurrence_time]      [alert_last_occurrence_time], 
       a.[last_response_date]        [alert_last_response_date], 
       a.[last_response_time]        [alert_last_response_time], 
       a.[notification_message]      [alert_notification_message], 
       a.[include_event_description] [alert_include_event_description], 
       a.[database_name]             [alert_database_name], 
       a.[event_description_keyword] [alert_event_description_keyword], 
       a.[occurrence_count]          [alert_occurrence_count], 
       a.[count_reset_date]          [alert_count_reset_date], 
       a.[count_reset_time]          [alert_count_reset_time], 
       a.[job_id]                    [alert_job_id], 
       a.[has_notification]          [alert_has_notification], 
       a.[flags]                     [alert_flags], 
       a.[performance_condition]     [alert_performance_condition], 
       a.[category_id]               [alert_category_id] 
FROM   msdb.dbo.sysalerts a 
       LEFT OUTER JOIN msdb.dbo.syscategories sc ON a.category_id = sc.category_id 
       LEFT OUTER JOIN msdb.dbo.sysnotifications sn ON ( = sn.alert_id ) 
       LEFT OUTER JOIN msdb.dbo.sysoperators o ON ( = sn.operator_id ) 
       LEFT OUTER JOIN msdb.dbo.sysjobs j ON j.job_id = a.job_id 
       LEFT OUTER JOIN msdb.dbo.sysmessages sm ON sm.error = a.message_id
   and sm.msglangid = SERVERPROPERTY('LCID')

And here is the sample result: