Setting Up Database Mail for Alerts

An alert is useful only if its message leaves the server. Setting up Database Mail takes more than an SMTP address: the profile, permissions, SQL Server Agent setting, and delivery queue must agree. Test the complete route before an incident.

Gather Requirements Before Setting Up Database Mail

Before setting up Database Mail, get the approved SMTP host, port, encryption requirement, sender identity, authentication method, and destination policy from the mail team. Do not reuse a personal mailbox password. Decide whether the sender is for all instance notifications or a narrower purpose. Record who owns credential rotation and how delivery failure is reported.

I ask the recipient team which address is monitored during nights and holidays. A test sent to a single administrator’s inbox can pass while the actual on-call route is unconfigured. Which inbox must receive the first real storage alert? Use that as the acceptance target. Keep sensitive SMTP credentials out of scripts and change tickets that do not need them. Confirm that the mail team accepts this sender and that the route permits the on-call address.

Create an Account and Profile

A Database Mail account holds SMTP connection information. A profile groups one or more accounts and is what callers select when sending. Use the Database Mail Configuration Wizard or approved T-SQL configuration procedure to create them. Give the profile a descriptive name and restrict access according to the sending identities. A public default profile is convenient only when its broad use is intended.

I document both the profile and account because they solve different problems. A profile can exist without a working account, and an account can exist without a profile the Agent uses. Review the configuration after setting up Database Mail rather than trusting a wizard’s final page.

Enable and Test the Feature While Setting Up Database Mail

Confirm Database Mail XPs is enabled under the organization’s configuration policy. Send a test with sp_send_dbmail from an authorized account and the selected profile to a monitored recipient. Use a subject that clearly marks it as a test and includes the instance name. Confirm it reaches the destination, not merely that the procedure queued it.

I record the mail item identifier and delivery result. A queued item is only halfway to an inbox. The queue does not get to call that delivery. If a test fails, inspect the queue and event log before changing SMTP settings at random. It is possible to have a working SQL procedure and a rejected mail server connection at the same time.

Inspect the Mail Items

The sysmail_allitems view shows recent messages and their sent status. Query it in msdb to see whether a test is sent, unsent, retrying, or failed. Keep recipients and message content protected when sharing diagnostics. The query below limits the result to recent items and columns useful for triage.

I check the specific test item first. A busy instance can have unrelated old failures, and chasing those before identifying the current item wastes time. If an item is still unsent, move to queue health. If it failed, read the linked event messages for the actual SMTP or authentication error.

SELECT TOP (20)
    mailitem_id,
    sent_status,
    send_request_date,
    sent_date,
    profile_id
FROM msdb.dbo.sysmail_allitems
ORDER BY mailitem_id DESC;

Read the Queue and Event Log

Database Mail processes messages through its queue and external program. Use sysmail_help_queue_sp to inspect queue status, then read sysmail_event_log for warnings and errors. A stopped queue, failed external process, authentication rejection, and recipient problem require different fixes. Do not restart services first and erase the best clue.

The commands below are read-only diagnostics. They do not send mail. I save the event time, item identifier, and relevant error before escalating to the mail team. That gives the team a concrete SMTP problem to investigate instead of the broad report that email is broken.

EXEC msdb.dbo.sysmail_help_queue_sp @queue_type = 'Mail';

SELECT TOP (20)
    log_date,
    event_type,
    description,
    mailitem_id
FROM msdb.dbo.sysmail_event_log
ORDER BY log_date DESC;

Connect SQL Server Agent

SQL Server Agent has its own Alert System setting for Database Mail. Select the Database Mail system and the intended profile, enable the mail profile, and follow the documented Agent restart procedure so the setting takes effect. Create an operator and attach it to alerts or job notifications. The Agent service identity must be able to use the profile under the chosen access model.

I test an Agent notification after the direct Database Mail test. The direct test proves the mail profile can send, but it does not prove Agent selected that profile or the operator has a valid route. Those are separate links in the chain.

Test the Failure Path Safely

Use a controlled harmless Agent test alert or job notification rather than generating a real corruption or high-severity error. Confirm the operator receives a message with enough context to act. Check that the on-call address and any escalation route work. An alert definition with no reachable operator is just an attractive row in msdb.

I review the test from the recipient side, not only from SQL Server. The message can be delivered but trapped in a mailbox rule. Keep the test date and result in the instance build record. After SMTP or mail policy changes, repeat the end-to-end test.

Keep the Route Maintained After Setting Up Database Mail

Monitor the queue and failed items, review profile ownership, and update credentials or certificates under the mail team’s process. Remove stale operators when people change roles. Include Database Mail configuration in instance rebuild documentation, while keeping secrets in the approved vault. A restored user database does not recreate the surrounding alert route.

A mail path that worked once can fail months later. I add a periodic safe test and an independent check for a stopped SQL Server Agent. The same system cannot notify you of its own outage through a service that is no longer running. That blind spot deserves a separate monitor. Keep its owner and test record with the Database Mail profile details, so a rebuild restores both notification paths.

Related reading on this blog: Database Mail Error: The SMTP Server Requires a Secure Connection or the Client Was Not Authenticated. The Server Response Was: 5.5.1: Using Gmail and Database Mail Breaks with TLS 1.0 Disabled Discovery: Notes from the Field #128.

Database Mail is not ready when a message is queued, it is ready when an Agent alert reaches its operator.

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