Configure Database Mail and Send a Test Message

calendar Jul 1, 2026 / · 12 min read · sql server database mail sp_send_dbmail sysmail_add_account_sp sysmail_event_log msdb sql server agent sys.configurations sys.databases sysmail_allitems administration scripts  ·
Share on: linkedin copy

Database Mail is switched off on a new instance, and sp_send_dbmail cannot work until an SMTP account, a profile that holds it, and a grant that lets users send through that profile all exist in msdb. The script below enables the feature, builds those three objects, sends a test message, and reads the queue and the event log to show what happened to it.

Purpose and Overview

The Database Mail Configuration Wizard in SSMS builds the same account, profile and security objects this script does, but a wizard run leaves nothing to review, store in source control or replay on the next instance. A T-SQL script does, which makes it the better tool when more than one server needs identical mail settings, and the wizard stays useful for one-off changes and for the Configure System Parameters page.

Mail leaves SQL Server through one pathway. An account holds the connection details for one SMTP server. A profile is an ordered collection of accounts, and applications name the profile rather than an account. A principal-to-profile grant decides who may use that profile. Because the account and the profile are separate objects, an administrator can add, remove or reconfigure accounts without changing application code or job steps.

sp_send_dbmail does not deliver anything itself. It places a request on a Service Broker queue and returns immediately, and the external Database Mail program (DatabaseMail.exe) reads the request and talks to the SMTP server. The outcome goes back through a status queue, which updates the message row. The practical result is that a successful call to sp_send_dbmail proves only that the message was queued. Whether it was delivered is answered by msdb.dbo.sysmail_allitems and msdb.dbo.sysmail_event_log, which the last two queries in the script read.

The script covers five jobs in order: enable the Database Mail XPs option, create the account, create the profile, link the two with a sequence number, and make the profile public and default. It then sends a message and checks the result. The configuration is deliberately the simplest valid one, a single account in a single profile, so each moving part is easy to see.

Code Breakdown

The complete script, from enabling the feature 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 1b: Confirm the option and that Service Broker is enabled in msdb
11SELECT name, value, value_in_use
12FROM sys.configurations
13WHERE name = N'Database Mail XPs';
14
15SELECT name, is_broker_enabled
16FROM sys.databases
17WHERE name = N'msdb';
18
19-- Step 2: Create the SMTP account
20EXEC msdb.dbo.sysmail_add_account_sp
21    @account_name    = 'DBA Alerts Account',
22    @description     = 'Primary SMTP relay for SQL Server Agent alerts',
23    @email_address   = 'sqlserver@example.com',
24    @display_name    = 'SQL Server DBA',
25    @mailserver_name = 'smtp.example.com',
26    @port            = 25,
27    @enable_ssl      = 0;
28
29-- Step 3: Create the mail profile
30EXEC msdb.dbo.sysmail_add_profile_sp
31    @profile_name = 'DBA Alert Profile',
32    @description  = 'Profile for SQL Server Agent operator emails and alerts';
33
34-- Step 4: Add the account to the profile (lowest sequence number is tried first)
35EXEC msdb.dbo.sysmail_add_profileaccount_sp
36    @profile_name    = 'DBA Alert Profile',
37    @account_name    = 'DBA Alerts Account',
38    @sequence_number = 1;
39
40-- Step 5: Make the profile public and the default public profile
41EXEC msdb.dbo.sysmail_add_principalprofile_sp
42    @profile_name   = 'DBA Alert Profile',
43    @principal_name = 'public',
44    @is_default     = 1;
45
46-- Step 6: Send a test email
47EXEC msdb.dbo.sp_send_dbmail
48    @profile_name = 'DBA Alert Profile',
49    @recipients   = 'dba@example.com',
50    @subject      = 'SQL Server Database Mail - Test Message',
51    @body         = 'Database Mail is configured and operational.',
52    @body_format  = 'TEXT';
53
54-- Step 7: Check the status of every message Database Mail has processed
55SELECT
56    m.mailitem_id,
57    m.recipients,
58    m.subject,
59    m.sent_status,
60    m.sent_date,
61    m.send_request_date
62FROM msdb.dbo.sysmail_allitems AS m
63ORDER BY m.send_request_date DESC;
64
65-- Step 8: Check the event log for errors, warnings and confirmations
66SELECT
67    el.log_id,
68    el.event_type,
69    el.log_date,
70    el.description,
71    el.process_id,
72    el.mailitem_id,
73    el.account_id
74FROM msdb.dbo.sysmail_event_log AS el
75ORDER BY el.log_date DESC;
76
77-- Audit: list the accounts (passwords are not returned)
78EXEC msdb.dbo.sysmail_help_account_sp;
79
80-- Audit: list the profiles
81EXEC msdb.dbo.sysmail_help_profile_sp;

Enabling Database Mail XPs

The option is off by default, with a value of 0, and it is an advanced option, so show advanced options must be set first. The change takes effect on RECONFIGURE without a restart, and configuring Database Mail requires membership in the sysadmin fixed server role. Setting the option back to 0 does not stop a running mail process at once: it keeps running and sending until it has been idle for the time set in the DatabaseMailExeMinimumLifeTime option.

Step 1b reads the result back from sys.configurations, where value is what sp_configure calls config_value and value_in_use is its run_value. If the two differ, RECONFIGURE did not run or failed. The same step checks is_broker_enabled for msdb in sys.databases. Service Broker must be enabled in msdb for Database Mail to work, and a value of 0 means sent messages stay on the transmission queue.

Creating an SMTP account with sysmail_add_account_sp

An account holds the details for exactly one e-mail server. @email_address is the address the message is sent from, @display_name is the name shown in the From field, and an optional @replyto_address sends replies somewhere else, such as to the DBA team rather than to an unattended mailbox. @mailserver_name accepts a name or an IP address, and the server must be able to resolve it. The port defaults to 25 and @enable_ssl defaults to 0.

Authentication is decided by three parameters. With @username left NULL, Database Mail does not authenticate at all, which is what the script does. With @username and @password supplied, the account uses SMTP authentication, and those credentials belong to the mail server rather than to SQL Server or the network. With @use_default_credentials = 1, Database Mail connects with the credentials of the Database Engine service account. Basic authentication against Exchange Online is disabled in all tenants, so an Exchange Online relay needs a different method.

Creating a profile with sysmail_add_profile_sp

A profile holds any number of accounts. Callers refer to it by name or by the profile_id the procedure generates. The name must be unique in the instance, or the procedure returns an error. The name and description can be changed later with sysmail_update_profile_sp, and the profile_id stays the same for the life of the profile.

Linking account to profile with sysmail_add_profileaccount_sp

Both objects must already exist, so this call comes after the previous two. The sequence number sets the order of use: for a new message Database Mail starts with the account that has the lowest number and moves to the next one if that account fails. If the highest-numbered account also fails, Database Mail pauses for the interval set by AccountRetryDelay in sysmail_configure_sp and starts again from the lowest number. AccountRetryAttempts sets how many times the external process tries each account. Two accounts sharing a sequence number are not an ordering: only one of them is used for a given message and there is no guarantee which.

Granting profile access with sysmail_add_principalprofile_sp

A new profile is private and available only to members of sysadmin. A @principal_name of public (or a @principal_id of 0) makes it a public profile available to every user in msdb. Public access is not enough on its own: the user must also be a member of the DatabaseMailUserRole database role in msdb, because EXECUTE on sp_send_dbmail defaults to that role only. A private profile is granted to named users or roles instead, which is the choice when only some workloads should be able to send mail.

@is_default = 1 makes the profile the default for the principal. When sp_send_dbmail is called without a profile name, it uses the caller's default private profile, then the default public profile for msdb, and returns an error if neither exists. A principal can have only one default profile.

Sending the test with sp_send_dbmail

At least one of @recipients, @copy_recipients or @blind_copy_recipients is required, and each takes a semicolon-delimited list. The @body_format is TEXT by default and can be HTML. When the message is queued the procedure returns the mailitem_id, which can be captured with the @mailitem_id output parameter and used to look the message up in the views below. If sp_send_dbmail itself raises an error, the message was never submitted and nothing appears in sysmail_event_log.

Diagnosing delivery with sysmail_allitems and sysmail_event_log

sysmail_allitems has one row per message processed by Database Mail. Its sent_status column holds sent, unsent, retrying or failed. unsent means Database Mail is still working on the message, either because the message is new and the queue has not reached it or because the external program is not running. retrying means the SMTP server could not be contacted and Database Mail is trying the other accounts in the profile and then waiting out the retry delay. failed means Database Mail could not send the message. The narrower views sysmail_sentitems, sysmail_unsentitems and sysmail_faileditems split the same rows by status.

sysmail_event_log holds the messages the Database Mail system itself produced: errors, warnings, informational messages, success messages and internal messages, depending on the logging level. The default level, Extended, records errors, warnings and informational messages. Switching to Verbose adds success and internal messages and is meant for troubleshooting. Entries appear when Service Broker cannot activate the external program, when the program hits a network error, or when the SMTP server refuses a message.

Warnings can appear for a message that was delivered. While an account is failing during retries, Database Mail holds the error text, and if a later attempt succeeds the accumulated errors are logged as warnings that carry the account_id. If delivery ultimately fails, the earlier warnings are logged as a single error with no account_id, since every account failed. To trace a specific failure, find its mailitem_id in sysmail_faileditems and filter the event log on it.

Key Benefits and Use Cases

  • Agent alert delivery - SQL Server Agent can notify operators when an alert fires or when a scheduled job succeeds or fails, once Agent is pointed at a Database Mail profile.
  • Custom monitoring alerts - a Transact-SQL job step can call sp_send_dbmail to send the result of a query to a list of recipients, without configuring Agent mail at all.
  • Queued, background delivery - the external process delivers outside the engine, and SQL Server keeps queuing messages if that process stops or fails. Queued messages go out once the process or the SMTP server is back.
  • Failover between mail servers - a profile can hold several accounts in sequence, so one unreachable SMTP server does not stop delivery.
  • Audit trail - copies of messages and attachments are kept in msdb, and activity is logged to the SQL Server error log, the Windows Application event log and tables in msdb.
  • Access control by profile - private profiles restrict sending to named users and roles, while public profiles open it to the whole DatabaseMailUserRole.

Performance Considerations

  • Delivery is asynchronous: sp_send_dbmail returns as soon as the request is on the Service Broker queue, so a job step is not held waiting for the SMTP conversation.
  • Message history grows msdb: messages and their attachments stay in msdb until someone deletes them. sysmail_delete_mailitems_sp removes them permanently, takes @sent_before and @sent_status arguments (at least one is required), and deletes the attachments with the messages.
  • The event log is cleaned separately: deleting mail items does not delete the matching rows in sysmail_event_log. sysmail_delete_log_sp handles those.
  • Service Broker must be enabled in msdb: if is_broker_enabled is 0, queued messages are not processed.
  • Large query results cost resources: @query_no_truncate stops long columns being cut at 256 characters, and with large amounts of data it uses extra resources and can slow the server. The query also runs in a separate session, so variables from the calling script are not visible to it.
  • Attachments are capped: files are limited to 1 MB each by default, and the limit can be changed with sysmail_configure_sp. Database Mail runs as the Database Engine service account, so that account needs access to any folder an attachment comes from.

Practical Tips

  • After any configuration change, read sysmail_event_log ordered by log_date DESC before calling the problem solved. A clean return from sp_send_dbmail only means the message was queued.
  • Keep a retention job for sysmail_delete_mailitems_sp and sysmail_delete_log_sp on a schedule so the history does not grow without limit.
  • Add a second account to the profile at sequence 2, on a different SMTP server, so a relay outage falls through to it with no change to callers.
  • On an availability group using a contained availability group, enable Database Mail XPs on every replica by connecting to each instance, not the listener, and then configure the mail objects through the listener so they stay synchronized.
  • Capture @mailitem_id from the test call, then look it up in sysmail_allitems and sysmail_event_log to follow that one message.
  • Run sysmail_help_account_sp and sysmail_help_profile_sp on an existing instance before running the setup, because a profile name must be unique in the instance and a duplicate makes the call fail.

Conclusion

Four sysmail_* calls inside msdb build a working configuration: account, profile, profile-account link and principal grant, after Database Mail XPs is switched on. The same script ends with the queries that show whether a message left the server, sysmail_allitems for status and sysmail_event_log for the reason when it did not. It belongs in the toolkit as the repeatable first step before Agent alerts or any custom monitoring job depends on mail.

References

Posts in this series