SQL Server Agent Alerts: Balancing Alert Thresholds and Notification Noise

Direct answer

Configure sp_add_alert for error severities 19 through 25, set @delay_between_responses to throttle repeated notifications, and map each alert to a small, explicit operator list in msdb.dbo.sysnotifications. Keep operators out of sysadmin; grant only DatabaseMailUserRole in msdb. Retain native Agent alerts unless you need correlation logic that thresholds cannot express.

Confirmed facts versus likely explanation

Confirmed: SQL Server Agent alerts evaluate severity or performance-condition thresholds. sp_add_alert accepts @delay_between_responses in seconds, which is the native mechanism for limiting repeated alerts. Operators must exist in msdb.dbo.sysoperators, be associated with a Database Mail profile, and hold DatabaseMailUserRole or sysadmin to receive notifications. Notification routing is stored in msdb.dbo.sysnotifications.

Likely explanation for noise: Alerts fire on every matching event without built-in deduplication beyond the response delay. Low severities, transient performance-counter spikes, and broad operator lists generate duplicate or non-actionable email.

Steps for this case

1. Verify Database Mail is configured and identify the profile operators will use.
2. Create or update each operator with the least privilege needed:

USE msdb;
GO
EXEC dbo.sp_add_operator @name = N'DBA_OnCall', @enabled = 1, @email_address = N'[contact removed]';
-- Do not add operators to sysadmin.

1. Add high-severity alerts with a response delay. Example for severity 20:

EXEC dbo.sp_add_alert
  @name = N'Severity 20 Error',
  @severity = 20,
  @delay_between_responses = 300,
  @include_event_description_in = 1;

1. Limit recipients to the minimal operator set:

EXEC dbo.sp_add_notification
  @alert_name = N'Severity 20 Error',
  @operator_name = N'DBA_OnCall',
  @notification_method = 1;

Review existing alerts and remove or raise thresholds for noisy low-severity rules.

Verification

• Query msdb.dbo.sysoperators to confirm only intended operators exist.
• Query msdb.dbo.sysnotifications to confirm each alert maps to the minimal operator list.
• Raise a test event matching the alert severity and confirm one email arrives; repeat within the delay window and confirm no additional notification until the interval elapses.

Assumptions and uncertainty

This guidance assumes a supported SQL Server release with Database Mail enabled and SQL Server Agent running. Performance-counter thresholds require a baseline; without knowing your normal counter range, a fixed threshold may still produce false positives. Whether @delay_between_responses should be 300, 600, or another value depends on your incident-response SLA.

Missing diagnostic detail: What is the maximum acceptable delay between a recurring critical event and operator notification? This determines the response-delay value and whether native throttling is sufficient.

Extended Events consideration

Extended Events can filter more granularly than Agent alerts, but it requires a custom target or job that sends Database Mail. Replace Agent alerts only when you need event correlation or suppression logic that @delay_between_responses cannot provide; otherwise the added maintenance outweighs the noise benefit.