What to Alert On, and What to Let Sleep

SQL Server alerting should ask for attention only when a useful response is needed. Every notification needs an owner, a reason for its urgency, and a clear first action.

Start With the Required Action

Before creating an alert, write down what the recipient should do. Identify the business impact and how long the response can wait. If nobody can name an action, collect the information for a report.

A failed critical load near its business deadline may need immediate attention. A slowly growing table may need a planned capacity task. The technical event alone does not decide urgency.

Assign an owner and an escalation route. Include enough context to avoid starting the investigation from zero. The recipient should know the affected service, the observed condition, and the first relevant check.

Measure What Is Missing

Successful completion has a deadline, even when the scheduler reports no explicit failure. A disabled backup job can remain quiet indefinitely. Check evidence of completed work against the required schedule.

SELECT d.name,
       MAX(b.backup_finish_date) AS latest_recorded_full_backup
FROM sys.databases AS d
LEFT JOIN msdb.dbo.backupset AS b
  ON b.database_name = d.name AND b.type = 'D'
WHERE d.database_id > 4
  AND d.state_desc = 'ONLINE'
GROUP BY d.name
ORDER BY d.name;

This is an inventory, not a universal backup alert rule. Different databases may have different schedules, replicas, or backup destinations. Match the check to the actual backup policy and retained history.

Monitor the collector's own freshness from outside the target instance. Missing monitoring data is an unknown state, not a healthy state. Distinguish planned maintenance from an unexpected loss of visibility.

Use Duration and Context

A short spike can be normal, while sustained pressure can threaten a service objective. Consider duration, rate of change, and workload context before setting thresholds. A single counter crossing a copied number rarely explains impact.

SELECT r.session_id, r.blocking_session_id,
       r.status, r.wait_type, r.wait_time,
       r.total_elapsed_time, DB_NAME(r.database_id) AS database_name
FROM sys.dm_exec_requests AS r
WHERE r.blocking_session_id > 0
ORDER BY r.wait_time DESC;

This shows currently blocked requests, subject to monitoring permissions. Repeated samples can establish persistence, but one sample cannot. Correlate blocking with affected business requests before deciding whether it needs an overnight response.

Do not automatically terminate sessions from a broad blocking rule. The blocking transaction may be important, and rollback can take time. Give the responder evidence and an approved decision process.

Separate Symptoms From Incidents

One storage problem may produce failed backups, slow queries, and application timeouts. Sending every symptom as an independent urgent message creates noise. Group related notifications around the affected service and time window.

SELECT DISTINCT v.volume_mount_point,
       CONVERT(decimal(18,2), v.total_bytes / 1073741824.0) AS total_gb,
       CONVERT(decimal(18,2), v.available_bytes / 1073741824.0) AS free_gb
FROM sys.master_files AS f
CROSS APPLY sys.dm_os_volume_stats(f.database_id, f.file_id) AS v;

This query covers volumes containing visible database files, not every backup or application destination. Free space is also different from free space inside a database file. State the coverage explicitly in the alert.

Combine remaining capacity with recent growth and operational lead time. A small percentage on a huge volume may allow more time than a larger percentage elsewhere. The useful question is whether action is needed before capacity runs out.

Design the Message and Recovery Signal

Include the instance, database or job, observation time, threshold, and observed value. Add the owner and a short diagnostic route. Avoid sending raw data dumps or sensitive query text to broad notification groups.

SELECT TOP (20) j.name AS job_name,
       h.run_date, h.run_time, h.run_duration, h.message
FROM msdb.dbo.sysjobhistory AS h
JOIN msdb.dbo.sysjobs AS j ON j.job_id = h.job_id
WHERE h.step_id = 0 AND h.run_status = 0
ORDER BY h.instance_id DESC;

This identifies recorded failed job outcomes, not whether the latest run has recovered. Check the most recent outcome before notifying repeatedly about an old failure. Keep history long enough to support the chosen review window.

Send a recovery update when it closes the operational question. Use a stable recovery condition so a noisy threshold does not alternate endlessly. Planned maintenance suppression should expire automatically.

Review the Calls You Created

Review every urgent notification for action taken, usefulness, duplication, and missing context. Remove or revise rules that repeatedly produce no response. Improve the underlying service when the same legitimate alert keeps returning.

Keep trends, minor deviations, and planned maintenance in a regular report. Protect urgent attention for conditions that require it. A quieter system is valuable only when important failures remain visible.

An alert is not a measurement with a loud sound, it is a request for a specific timely action.

This post was rewritten from scratch in September 2026. The original, published on 2008-04-01, was a short announcement about something that no longer exists. The address is the same, the subject is now something worth keeping.

Published by Pinal Dave on SQLAuthority. More of my work at pinaldave.com.