SQL Server Alerts Monitoring: Querying msdb.dbo.sysalerts for Event History

Summary: Learn how to retrieve and monitor SQL Server Agent alerts. This T-SQL script queries msdb.dbo.sysalerts to provide insights into alert severity, status, and occurrence history for troubleshooting.

Monitoring SQL Server Agent Alerts

The msdb.dbo.sysalerts table contains vital information about all alerts configured within SQL Server Agent. Monitoring these alerts is essential for proactive database administration, allowing you to track how often specific errors or severity levels are being triggered.

---

SQL Alert Inventory Script

Use the following query to generate a report of all configured alerts, their current status, and their most recent activity.

-- Retrieve SQL Server Alert Information
SELECT name
     ,event_source
     ,message_id
     ,severity
     ,[enabled]
     ,has_notification
     ,delay_between_responses
     ,occurrence_count
     ,last_occurrence_date
     ,last_occurrence_time
FROM msdb.dbo.sysalerts WITH (NOLOCK)
ORDER BY name
OPTION (RECOMPILE);
        

---

Column Explanations

Understanding the output of sysalerts helps in diagnosing whether your alerting system is tuned correctly:

• name: The unique identifier for the alert.
• severity: The SQL Server error severity level (0-25) that triggers this alert.
• [enabled]: A status of 1 means the alert is active; 0 means it is silenced.
• occurrence_count: The total number of times this alert has fired since it was created or reset.
• last_occurrence: Combined date and time of the most recent trigger—useful for identifying recent spikes in errors.
• delay_between_responses: The "wait period" (in seconds) that prevents an alert from flooding your inbox during a recurring error.

Best Practices for Querying msdb

This query utilizes the WITH (NOLOCK) hint to ensure that monitoring activity does not interfere with the SQL Agent's ability to write to the msdb database. Additionally, OPTION (RECOMPILE) is used to ensure the most efficient execution plan is generated regardless of the number of alerts configured.

Pro Tip: If you find that occurrence_count is high but you haven't received emails, check the has_notification column. If it is 0, you have an alert defined without an Operator assigned to it!