Have you ever wished your database could tell you what’s wrong or let you know when a task has completed? SQL Server’s Database Mail allows the database to send out messages over SMTP. Deanna Dicken shows you how to set up Database Mail and send some messages.
Introduction to Database Mail
Have you ever wished your database could talk to you, to tell you what’s
wrong or let you know when a task has completed? I certainly have. Fortunately,
SQL Server comes equipped with a utility for just such a task. SQL Server’s
Database Mail allows the database to send out messages over SMTP. Let’s have a
look at setting it up and sending some messages.
Overview of Database Mail
Database Mail is a robust SQL Server utility for enabling email from your
database. Email messages can be sent to multiple recipients, with multiple
attachments. You can even include a query to be executed and the results
attached to or included in the email.
The beauty is it doesn’t require an extended MAPI (Messaging
Application Programming Interface) client to be installed on the SQL Server.
All you need is an SMTP
(Simple Mail Transfer Protocol) server. Multiple SMTP servers can be utilized
The utility is enterprise-ready. Database Mail is cluster aware. It provides
for redundancy in profiles, accounts, and SMTP servers supporting both failover
and distribution of load. It performs asynchronously, queuing up messages via
Service Broker, allowing for decoupling from the email transport mechanism.
Copies of the emails and attachments are retained in the MSDB database and
usage is logged in the database as well as the event log. This allows for
auditing and support of Database Mail.
To provide security around Database Mail, users must be granted rights to
the profiles used by Database Mail (though they could be granted to public if
you choose). Also, the account executing it must be a member of the
DatabaseMailUserRole in the MSDB database.
Additional security is provided with regards to attachments. Database Mail
can be configured to limit the size of email attachments as well as the
allowable attachment extensions.
Database Mail Installation and Configuration
To prepare for Database Mail, you will need to determine what SMTP server
you want to utilize as well as the account that will be sending the email.
Additionally, the SMTP server will need to allow communication from the SQL
Server server and the account will need to be granted rights to send mail. SQL
Server can connect to the SMTP server using SSL (if required),
anonymous access (if allowed), or basic authentication.
Consideration must be given to the amount of traffic Database Mail will be
generating through your solution and if the selected SMTP server can handle the
additional traffic. Once the traffic volume is understood, you can estimate any
growth impact on MSDB, which serves as a repository for outgoing email messages
To control the growth of MSDB, clean up tasks can be scheduled to delete
messages and/or clean up the log. System stored procedures
sysmail_delete_mailitems_sp and sysmail_delete_log_sp are provided to assist with
Database Mail, which runs in MSDB, relies on SQL
Server Service Broker under the covers. By default, Service Broker is
enabled in MSDB. If it is not enabled in your MSDB, you will need to enable it.
This requires a database lock, which will require SQL Agent to be stopped.
Once SMTP and Service Broker are configured, Database Mail can be enabled on
your SQL Server instance. First, make sure the sending account is a member of
the DatabaseMailUserRole database role in the MSDB system database. Then, while
Server Management Studio, expand your connection to the instance. Navigate
to Management and right-click on Database Mail. Choose Configure Database Mail
to kick off the Database Mail Configuration Wizard. Alternatively, you can use
sp_configure to enable Database Mail. Let’s walk through the Configuration
Select the "Set up Database Mail by performing the following
tasks" option in the Configuration Wizard and answer "Yes" to
enable Database Mail.
After naming the profile, add the account or accounts the profile should use
to send mail. The accounts will be used in order should there be a problem with
one or more of them. Here you will also set your authentication mode to the
On this screen, you can specify access to the profiles as appropriate.
Finally, you can specify characteristics of Database Mail such as the retry
count, maximum file sizes, prohibited attachment extensions, and the logging
Review the summary and click finish. You’re ready to send mail.
Examples of Utilizing Database Mail
Now that Database Mail is set up on your SQL Server instance, let’s look at
a couple examples of utilizing Database Mail.
The following example shows a simple call to the sp_send_dbmail stored
procedure. You could put a call like this at the end of an application stored
procedure or SQL job to alert on success or failure of the processing for
EXEC msdb.dbo.sp_send_dbmail @profile_name = 'DBMail' , @recipients = '[email protected]' , @copy_recipients = '[email protected]' , @from_address = '[email protected]' , @subject = 'Success' , @body = 'SQL Job xyz completed successfully';
A more sophisticated example would be executing a query and including the
result set as an attachment to the email. Let say you’ve been concerned with
excessive locking in your database, but can’t spend all day looking at the
locking reports to see when excessive lock waits are occurring. So, you set up
a job that runs every 15 minutes and sends you the latest information on all
exclusive and intent exclusive locks for instance.
EXEC msdb.dbo.sp_send_dbmail @profile_name = 'DBMail' ,@recipients = '[email protected]' ,@from_address = '[email protected]' ,@query = 'SELECT resource_type, resource_database_id, request_mode, request_session_id FROM sys.dm_tran_locks WHERE request_mode IN (''IX'', ''X'')' ,@subject = 'Exclusive Locks' ,@attach_query_result_as_file = 1 ;
When this executes, the query will be run and the result set will be added
as an attachment to the email message. Now you can look through your emails for
trends in locking in one or more of your databases and investigate further if
Whether for administration or business application, Database Mail is an easy
and convenient way to add communication to your SQL processes. With consideration
for reliability, security, scalability, and supportability, this solution can
be used in shops small and large including those running SQL clusters.