Monitoring With What You Already Have

Useful SQL Server alerts can begin with components already available on the instance. The important work is selecting actionable events and proving that somebody receives the notification.

Check the Monitoring Foundation

SQL Server Agent is available with supported editions such as Standard and Enterprise, but not Express. Confirm that it runs and that scheduled jobs actually execute. An alert definition cannot compensate for a stopped Agent service.

SELECT servicename, startup_type_desc, status_desc,
       last_startup_time
FROM sys.dm_server_services;

This query needs the appropriate server monitoring permission. It reports service state, not successful mail delivery. Include an external availability check because the instance cannot reliably report its own complete outage.

Keep monitoring credentials limited to the access required. Store collected evidence somewhere that survives an instance failure. A monitoring design needs a way to distinguish silence from health.

Understand Which Errors Reach Agent

Severity-based SQL Server Agent alerts respond to matching events in the Windows application log. They do not observe every error returned to every client. That distinction matters when building severity 17 and 18 coverage.

Severity 19 and higher messages are logged by default. Selected lower-severity messages may need deliberate logging configuration. Review specific message behavior rather than assuming an alert catches everything at its configured severity.

SELECT message_id, severity, is_event_logged, text
FROM sys.messages
WHERE language_id = 1033
  AND severity BETWEEN 17 AND 25
ORDER BY severity, message_id;

Use this inventory to discuss coverage with the operational owner. Do not enable logging indiscriminately for every lower-severity message. Excessive event volume can obscure important evidence and consume log space.

Configure an Operator and Mail Path

Configure Database Mail and select the intended mail profile in SQL Server Agent's Alert System properties. Create an enabled operator for the responsible team. Use a maintained destination with a clear escalation process.

Send a controlled test through the complete Agent notification path. A successful Database Mail test alone does not verify the Agent profile or operator binding. Confirm receipt and record the test time.

SELECT name, enabled, email_address,
       last_email_date, last_email_time
FROM msdb.dbo.sysoperators
ORDER BY name;

Treat addresses and operational contacts as configuration that needs periodic review. A departed owner can leave a technically successful notification with nobody responding. Test again after mail, identity, or ownership changes.

Create Alerts With a Useful Response

In SSMS, create SQL Server event alerts for the selected severities and specific messages. Each severity alert matches one severity, so configure the intended coverage explicitly. Attach the operator and include enough event context for investigation.

SELECT a.name, a.enabled, a.message_id, a.severity,
       a.delay_between_responses, a.occurrence_count,
       o.name AS operator_name, n.notification_method
FROM msdb.dbo.sysalerts AS a
LEFT JOIN msdb.dbo.sysnotifications AS n ON n.alert_id = a.id
LEFT JOIN msdb.dbo.sysoperators AS o ON o.id = n.operator_id
ORDER BY a.name, o.name;

Use a response delay to reduce repeated messages without hiding a persistent incident. The notification should identify the instance and the next diagnostic step. An alert with no assigned action usually belongs in a report instead.

Test alert delivery using a controlled, documented lab procedure. Do not generate serious production errors merely to test a severity rule. Verify configuration and delivery without manufacturing an outage.

Add Failed Jobs and Missing Work

Configure essential jobs to notify the operator when they fail. Check job-step success and failure actions because a failed step can still lead to reported job success. Review retries and the final outcome together.

SELECT name, enabled, notify_level_email,
       notify_email_operator_id
FROM msdb.dbo.sysjobs
ORDER BY name;

The email notification level distinguishes never, success, failure, and completion settings. Verify the selected value against the job's purpose. A disabled job or stopped Agent may never produce a failed execution to notify you.

Add freshness checks for essential backups, loads, and reports. Compare the latest expected completion with an agreed deadline. Monitoring only explicit failures misses work that never started.

Use a Digest to Improve the System

Review recent alert counts, failed jobs, missed schedules, and notification tests each week. Identify repeated incidents and alerts that produced no useful action. Fix the underlying cause instead of training everybody to ignore the message.

Keep the digest separate from urgent notifications. Capacity trends and recurring maintenance concerns often need planned work rather than an overnight call. The system becomes useful when urgency matches the response it asks from people.

Monitoring is not a collection of enabled alerts, it is a tested route from a problem to an action.

This post was rewritten from scratch in September 2026. The original, published on 2009-08-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.