DBA Scripts: Get Agent Alerts and Operators

---

A SQL Server can raise a severity 19-25 error, the fatal, “something is genuinely broken” tier, corruption, out-of-resource conditions, hardware faults, and if there’s no alert configured for that severity, nothing happens. No email, no page, no ticket. The error sits in the error log where it will be found eventually, usually during the next health check or, worse, during the incident it caused.

Alerts alone aren’t enough either. An alert with no operator attached fires and has nowhere to send the notification, which looks identical to a working alert in every way except the one that matters.

This script checks both halves at once: which of the nine critical severities (17-25) have an enabled alert covering them, and whether every configured alert actually has a live operator attached to receive it.

---

Why Agent Alerts and Operators Matter

SQL Server Agent alerts are the only built-in mechanism that turns a severity-level error into an outbound notification, nothing else in the platform does this automatically:

• Severities 19-25 are the fatal tier. These aren’t warnings, they represent conditions SQL Server itself considers serious enough to potentially take a database or the instance down. Missing coverage here means the platform’s most serious errors are silent by default.
• An alert without an operator is a false sense of security. It shows up as “configured” in sysalerts, looks correct in a quick glance at SSMS, and does nothing when it fires, because there’s no notification target.
• Coverage doesn’t inherit. Configuring an alert for severity 21 does nothing for severity 22, each severity needs its own explicit alert. It’s easy to configure a handful and assume the rest are covered by extension.
• This is infrastructure most DBAs configure once, at build time, and never revisit, which is exactly the kind of setting that silently rots as operators change roles or leave the team.

---

Common Symptoms

• A serious error found in the error log during a routine check, with no corresponding alert or notification history.
• An operator who left the team months ago is still the only one attached to critical alerts.
• Confidence that “alerting is set up” that turns out to mean a handful of alerts exist, not that severity coverage is complete.
• An incident that could have been caught early was instead discovered by a user or a downstream failure.

---

When to Run This Script

• Routine SQL Server health checks
• Immediately after building a new instance, alerting is not configured out of the box
• After any change to the operators list (someone leaving the team, an email migration, a paging tool switch)
• As part of any DR or incident-response readiness review

---

The Script

Run the following script against your SQL Server instance.

/*
Script Name : Get-AgentAlertsAndOperators
Category    : monitoring
Purpose     : SQL Agent alerts and operators with severity gap analysis. Surfaces instances
              with no alerts for severity 19-25 (critical errors go unnoticed without these).
Author      : Peter Whyte (https://sqldba.blog/dba-scripts-get-agent-alerts-and-operators/)
Requires    : VIEW SERVER STATE, SELECT on msdb
*/
-- SAFE:ReadOnly
-- IMPACT:Low
SET NOCOUNT ON;

-- Severity coverage: which of 17–25 have at least one enabled alert?
WITH severity_alerts AS (
    SELECT DISTINCT severity
    FROM msdb.dbo.sysalerts
    WHERE enabled = 1
      AND severity BETWEEN 17 AND 25
),
severity_spine AS (
    SELECT 17 AS sev UNION ALL SELECT 18 UNION ALL SELECT 19 UNION ALL
    SELECT 20 UNION ALL SELECT 21 UNION ALL SELECT 22 UNION ALL
    SELECT 23 UNION ALL SELECT 24 UNION ALL SELECT 25
),
operators AS (
    SELECT COUNT(*) AS operator_count FROM msdb.dbo.sysoperators WHERE enabled = 1
)
SELECT * FROM (
    SELECT
        'severity_gap_check' AS result_type,
        sp.sev AS severity,
        CASE WHEN sa.severity IS NOT NULL THEN 'COVERED' ELSE 'NO ALERT' END AS coverage,
        CASE sp.sev
            WHEN 17 THEN 'Insufficient resources'
            WHEN 18 THEN 'Non-fatal internal error'
            WHEN 19 THEN 'Fatal resource error'
            WHEN 20 THEN 'Fatal error in current process'
            WHEN 21 THEN 'Fatal error in database processes'
            WHEN 22 THEN 'Fatal error: table integrity suspect'
            WHEN 23 THEN 'Fatal error: database integrity suspect'
            WHEN 24 THEN 'Fatal error: hardware error'
            WHEN 25 THEN 'Fatal error'
            ELSE ''
        END AS description,
        (SELECT operator_count FROM operators) AS enabled_operators,
        CASE WHEN sa.severity IS NULL AND sp.sev >= 19
             THEN 'CRITICAL — severity ' + CAST(sp.sev AS VARCHAR) + ' errors will not trigger an alert'
             WHEN sa.severity IS NULL
             THEN 'WARN — no alert for severity ' + CAST(sp.sev AS VARCHAR)
             ELSE 'OK'
        END AS status
    FROM severity_spine sp
    LEFT JOIN severity_alerts sa ON sa.severity = sp.sev

    UNION ALL

    -- All configured alerts (severity + error-number based)
    SELECT
        'configured_alert' AS result_type,
        a.severity AS severity,
        CASE a.enabled WHEN 1 THEN 'ENABLED' ELSE 'DISABLED' END AS coverage,
        a.name AS description,
        (SELECT COUNT(*) FROM msdb.dbo.sysnotifications n
         JOIN msdb.dbo.sysoperators op ON op.id = n.operator_id AND op.enabled = 1
         WHERE n.alert_id = a.id) AS enabled_operators,
        CASE
            WHEN a.enabled = 0
                THEN 'INFO — alert is disabled'
            WHEN NOT EXISTS (SELECT 1 FROM msdb.dbo.sysnotifications n
                             JOIN msdb.dbo.sysoperators op ON op.id = n.operator_id AND op.enabled = 1
                             WHERE n.alert_id = a.id)
                THEN 'WARN — no enabled operator assigned; alert fires but nobody is notified'
            ELSE 'OK'
        END AS status
    FROM msdb.dbo.sysalerts AS a
) results

ORDER BY
    CASE result_type WHEN 'severity_gap_check' THEN 1 ELSE 2 END,
    severity,
    result_type;

This builds a fixed spine of severities 17-25, left-joins it against enabled alerts to flag any gap, then unions in every configured alert with a check for whether it has a live, enabled operator attached, returning one combined result set ranked severity coverage first, individual alert detail second.

---

How To Run From The Repo

Clone DBA Tools, initialize and run the script:

# Clone dba-tools repo:
git clone https://github.com/peterwhyte-lgtm/dba-tools

# Initialize environment:
cd dba-tools
.\Initialize-Environment.ps1

# Check severity 17-25 alert coverage and operator assignment:
.\run.ps1 Get-AgentAlertsAndOperators

# To run against a remote sql server:
.\run.ps1 Get-AgentAlertsAndOperators -ServerInstance SQLSERVER01

This script lives in the repo at:

• sql/monitoring/jobs/Get-AgentAlertsAndOperators.sql
• powershell/wrappers/monitoring/jobs/Get-AgentAlertsAndOperators.ps1

---

Example Output

A genuinely stark finding: zero alert coverage across all nine critical severities, and zero enabled operators, sysalerts is empty on this box. This is a lab instance, but it’s exactly the state a freshly built production server is in before anyone configures alerting, nothing here was staged to make the point.

---

Understanding the Results

• result_type = severity_gap_check rows are the headline check, one per severity 17-25. coverage = NO ALERT means that severity has zero enabled alerts watching for it.
• status does the prioritising for you: CRITICAL for any uncovered severity 19 and above (the truly fatal tier), WARN for 17-18 (serious but less immediately dangerous).
• enabled_operators on the gap-check rows reflects the instance-wide count of enabled operators, not per-alert, it’s there so a 0 jumps out immediately as “nobody would be notified even if alerts existed.”
• result_type = configured_alert rows (not present in this example, since none exist on this box) show each individually configured alert with its own operator-assignment check, WARN if the alert is enabled but has no live operator attached.

---

Common Causes

Alerting typically ends up missing for one of two reasons. Either it was never configured at build time, SQL Server ships with zero alerts and zero operators out of the box, so unless someone explicitly runs through the setup, this state is the default, not an anomaly. Or it was configured once, correctly, and then quietly broken later: an operator’s email address changes, the person leaves the team and their account gets disabled, or a migration to a new paging tool never updated the SQL Agent operator list to match.

---

How to Fix Missing Alert Coverage

Create an operator first (an alert with nowhere to send its notification is nearly as useless as no alert):

EXEC msdb.dbo.sp_add_operator
    @name = N'DBA Team',
    @enabled = 1,
    @email_address = N'dba-team@example.com';

Then add an alert per missing severity, repeating for each gap the script surfaces:

EXEC msdb.dbo.sp_add_alert
    @name = N'Severity 019 - Fatal Resource Error',
    @message_id = 0,
    @severity = 19,
    @enabled = 1,
    @delay_between_responses = 60,
    @notification_message = N'Severity 19 error occurred.';

EXEC msdb.dbo.sp_add_notification
    @alert_name = N'Severity 019 - Fatal Resource Error',
    @operator_name = N'DBA Team',
    @notification_method = 1;  -- 1 = email

Repeat for each of 19 through 25 at minimum, then 17 and 18 if you want the earlier warning tier covered too. SQL Server Agent must be running for any of this to fire.

---

Best Practices

• Configure alerts for every severity 19-25 as a standard part of any new instance build, don’t leave this to be discovered missing later.
• Always attach at least one enabled operator to every alert, an alert with no operator gives a false sense of coverage.
• Review the operator list whenever team membership changes, a departed team member left as the sole operator is a silent gap.
• Re-run this script periodically, not just at initial setup, alerting infrastructure decays quietly as people and tools change around it.

---

Related Scripts

You may also find these scripts useful:

• SQL Agent and Jobs (hub)
• Job Schedules and Duration Trends
• SQL Agent Job Failure Summary
• SQL Agent Job Overview
• DBA Scripts: The Complete Guide, the map across every script on this site

---

Frequently Asked Questions

Summary

Alert coverage is one of those checks that’s invisible right up until the moment it isn’t, a severity 21 error either has somewhere to go or it sits quietly in the error log until someone happens to look. This script turns “is alerting actually configured” from an assumption into a two-second answer: nine severities checked, every configured alert’s operator assignment verified, in one query.

Run it right after any new build, and again any time the operator list might have drifted, because unlike most misconfigurations, this one only ever shows up the day you actually needed it.