Configure Database Mail and Send a Test Message

calendar Jul 1, 2026 / · 11 min read · sql server database mail sp_send_dbmail sysmail_add_account_sp sysmail_event_log sysmail_mailitems sysmail_account sysmail_profile msdb sql server agent administration scripts  ·
Share on: linkedin copy

Database Mail ships disabled; enabling it and wiring up an SMTP account, a profile, and a public principal grant are the steps that stand between a fresh SQL Server instance and sp_send_dbmail working reliably. The configuration script below executes all four setup calls inside msdb, fires a test message, and queries msdb.dbo.sysmail_event_log to confirm the message reached the SMTP relay — all in one repeatable script.

Purpose and Overview

Sending email from SQL Server — for Agent job failures, custom monitoring alerts, or ad hoc DBA notifications — runs through a single pathway: an SMTP account registered in msdb, wrapped in a named profile, and exposed to the Database Mail service via a principal-profile grant. When any of those three links is missing or misconfigured, sp_send_dbmail accepts the call and silently queues the message, but the mail never leaves the server. The only diagnostic path is msdb.dbo.sysmail_event_log.

Database Mail replaced SQL Mail, which depended on a MAPI client installed on the server, beginning with SQL Server 2005. The current architecture runs as an external host process (DatabaseMail.exe) that the SQL Server service launches independently, so a crash in the mail process does not affect the database engine. Messages queue in msdb.dbo.sysmail_mailitems before dispatch, which means every email attempt — sent, failed, or retrying — is auditable after the fact without reading the Windows event log or the SQL Server error log.

Four sysmail_* stored procedures build a minimal working configuration: one call to create the SMTP account, one to create the profile, one to bind the account to the profile with a sequence number, and one to grant the public database role access so sp_send_dbmail can queue mail without requiring sysadmin. This script creates the simplest valid configuration — one account, one profile, one principal grant — and documents the diagnostic queries used to confirm delivery.

The underlying system tables — msdb.dbo.sysmail_account, msdb.dbo.sysmail_profile, and msdb.dbo.sysmail_profileaccount — persist the configuration across service restarts and can be queried at any time to audit existing accounts and profiles without opening the Database Mail Configuration Wizard in SQL Server Management Studio.

Code Breakdown

The complete configuration and test script, from enabling the feature extension through delivery verification:

 1USE msdb;
 2GO
 3
 4-- Step 1: Enable Database Mail XPs (requires sysadmin)
 5EXEC sp_configure 'show advanced options', 1;
 6RECONFIGURE;
 7EXEC sp_configure 'Database Mail XPs', 1;
 8RECONFIGURE;
 9
10-- Step 2: Create the SMTP account
11EXEC msdb.dbo.sysmail_add_account_sp
12    @account_name    = 'DBA Alerts Account',
13    @description     = 'Primary SMTP relay for SQL Server Agent alerts',
14    @email_address   = 'sqlserver@example.com',
15    @display_name    = 'SQL Server DBA',
16    @mailserver_name = 'smtp.example.com',
17    @port            = 25,
18    @enable_ssl      = 0;
19
20-- Step 3: Create the mail profile
21EXEC msdb.dbo.sysmail_add_profile_sp
22    @profile_name = 'DBA Alert Profile',
23    @description  = 'Profile for SQL Server Agent operator emails and alerts';
24
25-- Step 4: Associate the account with the profile (sequence 1 = try first)
26EXEC msdb.dbo.sysmail_add_profileaccount_sp
27    @profile_name    = 'DBA Alert Profile',
28    @account_name    = 'DBA Alerts Account',
29    @sequence_number = 1;
30
31-- Step 5: Grant the public database role access; set as default profile
32EXEC msdb.dbo.sysmail_add_principalprofile_sp
33    @profile_name   = 'DBA Alert Profile',
34    @principal_name = 'public',
35    @is_default     = 1;
36
37-- Step 6: Send a test email
38EXEC msdb.dbo.sp_send_dbmail
39    @profile_name = 'DBA Alert Profile',
40    @recipients   = 'dba@example.com',
41    @subject      = 'SQL Server Database Mail - Test Message',
42    @body         = 'Database Mail is configured and operational.',
43    @body_format  = 'TEXT';
44
45-- Step 7: Check queued message status
46SELECT
47    m.mailitem_id,
48    m.recipients,
49    m.subject,
50    m.sent_status,
51    m.sent_date,
52    m.send_request_date
53FROM msdb.dbo.sysmail_mailitems AS m
54ORDER BY m.send_request_date DESC;
55
56-- Step 8: Check the event log for errors or confirmations
57SELECT
58    el.log_id,
59    el.event_type,
60    el.log_date,
61    el.description,
62    el.process_id,
63    el.mailitem_id,
64    el.account_id
65FROM msdb.dbo.sysmail_event_log AS el
66ORDER BY el.log_date DESC;
67
68-- Audit: Confirm the account was created
69SELECT
70    account_id,
71    name,
72    description,
73    email_address,
74    display_name,
75    mailserver_name,
76    port,
77    enable_ssl
78FROM msdb.dbo.sysmail_account;
79
80-- Audit: Confirm the profile was created
81SELECT
82    profile_id,
83    name,
84    description
85FROM msdb.dbo.sysmail_profile;

Enabling Database Mail XPs

Database Mail is implemented as an extended stored procedure feature and is disabled by default. Before any sysmail_* procedure will execute, the Database Mail XPs server configuration option must be set to 1 via sp_configure. Because it is an advanced option, show advanced options must be enabled first. Both changes take effect immediately on RECONFIGURE without a service restart. Running sp_configure 'Database Mail XPs' before and after the change confirms: run_value shows the active in-memory setting, while config_value shows the persisted value. sysadmin membership is required for both sp_configure calls.

Creating an SMTP account with sysmail_add_account_sp

The account holds every SMTP relay parameter: the server hostname, port, SSL flag, and the From-address that recipients see. @email_address is the address that populates the From header on every outgoing message from this account; @display_name is the human-readable name alongside it. On corporate relay configurations — a smart host or a gateway service — @enable_ssl is commonly set to 1 and the port set to 587 for STARTTLS submission. The procedure writes the account to msdb.dbo.sysmail_account. Authentication credentials, when required, are passed via @username and @password parameters and stored separately in msdb.dbo.sysmail_server.

Creating a profile with sysmail_add_profile_sp

A profile is a logical container that groups one or more SMTP accounts. The Database Mail service delivers through a profile, not directly through an account — this indirection lets a profile carry multiple accounts as ordered fallbacks. sysmail_add_profile_sp inserts a row into msdb.dbo.sysmail_profile. The @profile_name value is what callers pass to sp_send_dbmail and what SQL Server Agent uses in the operator configuration, so the name should be stable and descriptive. Renaming a profile after deployment requires updating every Agent operator record and job step that references it by name.

Linking account to profile with sysmail_add_profileaccount_sp

sysmail_add_profileaccount_sp inserts the join row into msdb.dbo.sysmail_profileaccount. The @sequence_number controls the order Database Mail tries accounts: sequence 1 is the first attempt. When delivery fails — the SMTP relay refuses the connection, authentication is rejected, or a timeout fires — Database Mail moves to the account registered at sequence 2, if one exists. For a single-account profile the sequence number is always 1. Adding a secondary relay as sequence 2 makes the profile resilient to primary SMTP outages without changing any calling code or Agent configuration.

Granting profile access with sysmail_add_principalprofile_sp

Without a principal-to-profile grant, only sysadmin members can use the profile. Passing @principal_name = 'public' extends access to every user who has connect rights to msdb, which is the normal setting for Agent and monitoring workloads. Setting @is_default = 1 designates this as the instance default profile, so sp_send_dbmail calls that omit @profile_name automatically route through it — useful for Agent job steps that use the operator-email mechanism without an explicit profile name. Environments with stricter access control can grant to a specific msdb database user rather than public.

Sending the test with sp_send_dbmail

sp_send_dbmail queues the message in msdb.dbo.sysmail_mailitems and returns immediately — delivery is asynchronous. A return with no error does not guarantee the message was accepted by the SMTP relay; it guarantees only that the row was inserted into the queue. The @body_format accepts TEXT (the default) or HTML. The @recipients parameter accepts semicolon-delimited addresses for multiple recipients; @copy_recipients and @blind_copy_recipients handle CC and BCC fields respectively. For Agent alert job steps, a @subject that includes @@SERVERNAME and the job name identifies the source instance at a glance.

Diagnosing delivery with sysmail_mailitems and sysmail_event_log

msdb.dbo.sysmail_mailitems shows every queued message and its current status. The sent_status column returns sent, unsent, failed, or retrying. A status of unsent immediately after the sp_send_dbmail call means the Database Mail external process is not running — check whether Service Broker is enabled on msdb with SELECT is_broker_enabled FROM sys.databases WHERE name = 'msdb'. A status of failed means the process ran but the SMTP relay rejected or could not be reached.

msdb.dbo.sysmail_event_log captures the process-level detail. The event_type column returns success, warning, or error. Error rows include the SMTP server's response text or the socket error in the description column — a 535 Authentication failed response pinpoints credential problems, a connection timeout message points to a firewall rule or wrong hostname, and a 550 Relay access denied identifies a relay restriction on the SMTP server. Reading this column is always faster than enabling a SQL Server trace or Extended Events session to diagnose the first setup failure.

Key Benefits and Use Cases

  • Agent alert delivery — once configured, SQL Server Agent operators receive job failure, success, retry, and cancellation notifications without any external tooling or custom polling.
  • Custom monitoring alerts — any T-SQL routine can call sp_send_dbmail directly to notify a DBA when a threshold is crossed, a backup fails, or a specific error number appears in the error log.
  • Asynchronous, reliable queuing — messages persist in sysmail_mailitems and survive service restarts; the Database Mail process retries on transient SMTP failures before marking a message as failed.
  • Auditable history — msdb.dbo.sysmail_sentitems, sysmail_unsentitems, and sysmail_failedrecipients retain a queryable history of every email attempt, including recipient-level failure detail.
  • Multi-account fallback — a profile carries ordered accounts so that relay failure on the primary SMTP server automatically falls through to a secondary without any caller changes.
  • Credential isolation — profiles decouple callers from SMTP credentials; changing the relay account or rotating a password requires updating sysmail_account once, not every job step or stored procedure that sends mail.

Performance Considerations

  • sp_send_dbmail does not block callers: the call returns as soon as the row is inserted into sysmail_mailitems, regardless of delivery status. Agent steps that trigger mail notification are not held waiting for the SMTP handshake to complete.
  • sysmail_mailitems grows without bounds by default: queued items are not purged automatically. Without a scheduled cleanup, the table accumulates every message ever sent and msdb grows indefinitely. Use msdb.dbo.sysmail_delete_mailitems_sp with a @sent_before parameter in a scheduled Agent job to enforce a retention window.
  • Service Broker must be enabled on msdb: the Database Mail external process communicates through a Service Broker queue. If Service Broker is disabled on msdb — for example after a restore from a backup taken with it off — mail queues but never dispatches. Confirm with SELECT is_broker_enabled FROM sys.databases WHERE name = 'msdb'.
  • High-volume loops flood the SMTP relay: calling sp_send_dbmail once per row inside a cursor or set-based loop generates one SMTP connection per message. Consolidate results into a single formatted body — using @query to embed a result set or building an HTML string — before the call to keep relay load manageable.
  • TLS version is OS-dependent: @enable_ssl = 1 uses the TLS version available to the Windows process, not a SQL Server setting. On older Windows Server versions, connections to modern relays requiring TLS 1.2 or TLS 1.3 may fail silently at the TLS handshake; verify the OS's Schannel configuration when mail works against an internal relay but fails against an external one.

Practical Tips

  • After any configuration change, read sysmail_event_log ordered by log_date DESC before declaring the problem resolved — a clean sp_send_dbmail return does not guarantee delivery.
  • Set @is_default = 1 on the principal-profile grant so SQL Server Agent can route notifications through the profile without requiring every operator record to name it explicitly.
  • Add a second SMTP account at sequence 2 in the profile for relay redundancy — a corporate smart host as primary and a direct submission relay as fallback covers most transient outages.
  • Create a scheduled Agent job that runs msdb.dbo.sysmail_delete_mailitems_sp nightly with a 30- or 90-day retention window to prevent msdb from growing without bound.
  • For HTML-formatted alert emails, set @body_format = 'HTML' and build the @body string with <table> markup; critical threshold values can be highlighted in bold or red to make the alert scannable at a glance in an email client.
  • Query msdb.dbo.sysmail_account and msdb.dbo.sysmail_profile before running the setup script on an existing instance to confirm the account and profile names do not already exist — duplicate names cause the sysmail_add_account_sp call to fail with a unique-constraint violation.

Conclusion

A working Database Mail configuration requires four sysmail_* procedures executed in order inside msdb: account, profile, profileaccount, and principalprofile. Once in place, sp_send_dbmail queues messages asynchronously and msdb.dbo.sysmail_event_log provides the diagnostic trail when delivery fails. Combined with a scheduled cleanup job for sysmail_mailitems and a multi-account profile for relay redundancy, this configuration supports SQL Server Agent alerts and custom T-SQL monitoring reliably across the lifetime of the instance.

References

Posts in this series