Using Database Mail in SQL Server 2012

Introduction

This article describes how to configure, test, and manage Database Mail in SQL Server 2012. Database Mail was first introduced as a new feature in SQL Server 2005 and replaces the SQL Mail feature found in previous versions.

Database Mail is a SQL Server component that provides developers and administrators with an enterprise solution for sending e-mail messages from the SQL Server Database Engine. While the primary use of Database Mail is to send alert messages to administrators, it can also be used to send the results of a query to end users, use it in Transact-SQL code, or even include file attachments from any available resource on the network.

Database Mail versus SQL Mail

As previously mentioned, database Mail was first introduced as a new feature in SQL Server 2005 and replaces the SQL Mail feature found in previous versions. SQL Mail is still retained in SQL Server 2005 and SQL Server 2008 for backwards compatibility meaning that the SQL Server Agent can still use both SQL Mail and the new Database Mail feature (recommended) to send e-mail messages.

SQL Mail was designed to utilize an Extended Messaging Application Programming Interface (Extended MAPI) to send e-mail notifications. This required users to install and configure an e-mail application (such as Outlook) that supported the Extended MAPI communication protocol on the computer that is running SQL Server. Database Mail, on the other hand, has no such requirement as it utilizes the Simple Mail Transfer Protocol (SMTP) instead of Extended MAPI to send mail. Database Mail not only simplifies email setup, but has many other benefits over SQL Mail, including the following:

• There is no requirement that an email client be installed on the SQL Server machine.
• Email is queued for later delivery if the mail server stops or fails.
• Multiple SMTP servers can be specified so that mail continues to be delivered in the event that one of the SMTP servers stops.
• Database Mail is cluster aware.

Receiving Email

The Database Mail feature is used to send e-mail messages from the SQL Server Database Engine. The only way to receive e-mail messages in SQL Server 2008 is by using the legacy stored procedures like sp_processmailfound in SQL Mail. Because there is no IMAP or POP3 support, Database Mail does not support receiving incoming e-mail messages. Receiving e-mail can present a security risk to the database server through the use of Denial-Of-Service (DOS) attacks or messages that contain harmful Transact-SQL like DROP DATABASE.

Configure Database Mail - (Database Mail Configuration Wizard)

Before using Database Mail, several configuration tasks need to be carried out. This section describes the steps necessary to successfully configure Database Mail which includes verifying that the SQL Server Agent is running, verifying that the Service Broker is enabled for the msdb database, enabling the Database Mail feature, and finally, creating an e-mail Profile and SMTP mail account using the Database Mail Configuration Wizard.

Verify SQL Server Agent is Running

Messages in Database Mail are sent by the SQL Server Agent. If the Agent is not running, the messages will be accumulated in the msdb database and sent when the Agent service gets started again.

Use the master..xp_servicecontrol system stored procedure to check the status of the SQL Server Agent process. If the Agent is stopped, you will want to start it in order to send messages through Database Mail.

USE master Go EXEC xp_servicecontrol N'QueryState', N'SQLServerAGENT'; Go Current Service State --------------------- Running.

Verify Service Broker is Enabled for the msdb Database

Database Mail relies on Service Broker for queuing e-mail messages. If Database Mail is stopped or if Service Broker message delivery is not activated in the msdb database, Database Mail queues messages in the database but cannot deliver the messages. In this case, the Service Broker messages remain in the Service Broker Mail queue. Service Broker does not activate the external program, so there are no log entries in sysmail_event_log and no updates to the item status in sysmail_allitems and the related views.

Verify that Service Broker is enabled for the msdb database by running the following query.

USE master Go SELECT is_broker_enabled FROM sys.databases WHERE database_id = DB_ID(N'msdb'); Go is_broker_enabled ----------------- 1

is_broker_enabled will be 1 if Service Broker is enabled for the given database (msdb), otherwise it will be 0.

If the Service Broker is disabled (is_broker_enabled = 0), run the following T-SQL to enable Service Broker for the msdb database:

USE master Go ALTER DATABASE msdb SET ENABLE_BROKER WITH ROLLBACK IMMEDIATE; Go

Enable Database Mail

Database Mail relies on a number of internal stored procedures. To reduce the surface area, these stored procedures are disabled on new installations of SQL Server which makes Database Mail not active by default. Users must explicitly enable these Database Mail stored procedures by using either one of the the following three methods.

1. sp_configure Stored Procedure
   To enable Database Mail using the sp_configure stored procedure, run the following.

   USE master Go EXEC sp_configure 'show advanced options', 1 Go Configuration option 'show advanced options' changed from 0 to 1. Run the RECONFIGURE statement to install. RECONFIGURE Go Command(s) completed successfully. EXEC sp_configure 'Database Mail XPs', 1 Go Configuration option 'Database Mail XPs' changed from 0 to 1. Run the RECONFIGURE statement to install. RECONFIGURE Go Command(s) completed successfully. EXEC sp_configure 'show advanced options', 0 Go Configuration option 'show advanced options' changed from 1 to 0. Run the RECONFIGURE statement to install. RECONFIGURE Go

2. Surface Area Configuration Facet of Policy-Based Management
   To enable Database Mail using the Surface Area Configuration Facet of Policy-Based Management, launch SQL Server Management Studio, right-click on the server that you intend to enable the Database Mail feature for and select Facets.

   Open Facets Dialog Box for Server

   On the Facets dialog box, select Surface Area Configuration from the Facet drop down list. Locate DatabaseMailEnabled in the Facet properties section and change its value from False to True and then click the Okbutton.

   Enable Database Mail

3. Database Mail Configuration Wizard
   The third method that can be used to enable the Database Mail feature is to do nothing here and wait until you run the Database Mail Configuration Wizard (next section). The Database Mail Configuration Wizard is used to create e-mail Profiles and SMTP Accounts; both of which are required in order to use Database Mail to send messages. If the Database Mail feature in not enabled, the Database Mail Configuration Wizard will prompt youto enable it when attempting to create the e-mail Profile and SMTP account.

   Enable Database Mail through the Database Mail Configuration Wizard

Create E-Mail Profile and SMTP Account

After enabling the Database Mail feature, the next step is to use the Database Mail Configuration Wizard to create an e-mail Profile and an SMTP Account through which to send emails. To start the Database Mail Configuration Wizard, in Object Explorer, expand the Management folder in SQL Server Management Studio, right-click Database Mail, and select Configure Database Mail.

Launch Database Mail Configuration Wizard

This will bring up the Database Mail Configuration Wizard welcome screen. Click Next to continue.

Welcome to Database Mail Configuration Wizard

On the Select Configuration Task screen, select Set up Database Mail by performing the following tasks:. Click Next to continue.

Select Configuration Task

If you haven't previously enabled Database Mail then you will receive a message box asking if you wish to enable the Database Mail feature. Click Yes to enable it.

Enable Database Mail

Next, on the New Profile screen, provide an e-mail Profile Name and an optional description of the profile, then select the Add button to configure an SMTP Account.

Create Profile Name and SMTP Account

On the New Database Mail Account screen, provide the required information for the outgoing mail server (SMTP). Fill in the Account Name, Description (optional), Email Address, Display name, Reply Email (optional), and SMTP Server Name and Port number. Then select the appropriate SMTP authentication for your server and click Next to continue.

Gmail Users If you do not have access to an SMTP server, consider using your Gmail or Yahoo! account. Note that if you are using Gmail as your outgoing mail server, the server name will be smtp.gmail.comand the port number to be used is 587 and not the default port number 25. Also, Gmail requires a secure connection so click the check-box This server requires a secure connection (SSL).

Configure SMTP Account

This will return you to the New Profile screen. Click Next to continue.

Return to the New Profile Screen

The Manage Profile Security screen allows you to set this profile as either public or private. A private profile is accessible only to specific users or roles. A public profile allows any user or role with access to the mail host database (msdb) to send e-mail using this profile. Note that in order to send Database Mail, users must also be a added to the DatabaseMailUsersRole database role in the msdb database.

You can also specify that this profile is a default profile. In this case, users or roles can send e-mail using the profile without explicitly specifying the profile name. If the user or role sending the e-mail message has a default private profile (use the Private Profiles tab on this screen), Database Mail uses that profile. If the user or role has no default private profile, sp_send_dbmail uses the default public profile for the msdb database. If there is no default private profile for the user or role and no default public profile for the database, sp_send_dbmail returns an error. Only one profile can be marked as the default profile.

Click Next to continue.

Manage Profile Security

Use the Configure System Parameters screen to specify Database Mail system parameters such as retry attempts or types of attachments that you do not want to send out by including the extensions of the files (Prohibited Attachment File Extensions). Click Next to continue.

Configure Database Mail System Parameters

Finally, review the list of steps the wizard is about to perform, and then click Finish to execute these actions.

Verify Actions to Perform

Verify that all actions completed successfully. Click Close to exit the Database Mail Configuration Wizard.

Verify All Actions Completed Successfully

Perform a quick test of the new e-mail Profile within SQL Server Management Studio. Right-click Database Mail and select Send Test E-Mail....

Send Test E-Mail

Supply a recipient email address and click Send Test E-Mail.

Supply Recipient Email Address

Check that you received the test email and click Ok to close the dialog box; otherwise click the Troubleshoot button to investigate any errors. In addition to the Troubleshooting button, you can also review the contents ofmsdb.dbo.sysmail_event_log.

SELECT * FROM msdb.dbo.sysmail_event_log; Go

Verify Email was Sent and Received

Configure Database Mail - (Transact-SQL)

The previous section included detailed instructions for setting up Database Mail using the Database Mail Configuration Wizard. Database Mail can also be configured using Transact-SQL (Database Mail stored procedures) to create profiles and accounts.

To create a Database Mail profile using Transact-SQL:

1. Create one or more Database Mail accounts for the profile using the msdb.dbo.sysmail_add_account_sp stored procedure.
2. Execute the stored procedure msdb.dbo.sysmail_add_profile_sp to create the profile, specifying:
   • The name of the profile to create.
   • An optional description of the profile.
3. For each account, execute msdb.dbo.sysmail_add_profileaccount_sp to add the account to the profile.
4. For each database role or user that will send mail using this profile, grant access to the profile by executing msdb.sysmail_add_principalprofile_sp (next section).

Example

The following example creates a Database Mail account and a Database Mail profile using Transact-SQL. The example then adds the account to the profile. Granting access to the profile to the AppUser database user will be performed in the section "Handling Security in Database Mail".

-- Create a Database Mail account EXEC msdb.dbo.sysmail_add_account_sp @account_name = 'iDevelopment SMTP Account' , @description = 'Administrative SMTP account for iDevelopment.info' , @email_address = 'dba@idevelopment.info' , @display_name = 'iDevelopment.info Database Support' , @replyto_address = 'dba@idevelopment.info' , @mailserver_type = 'SMTP' , @mailserver_name = 'relay.pair.com' , @port = 25 , @use_default_credentials = 0 , @username = 'jhunter' , @password = 'xxxxxxxx' Go -- Create a Database Mail profile EXEC msdb.dbo.sysmail_add_profile_sp @profile_name = 'iDevelopment.info Profile' , @description = 'Administrative profile for iDevelopment.info' Go -- Add the account to the profile EXEC msdb.dbo.sysmail_add_profileaccount_sp @profile_name = 'iDevelopment.info Profile' , @account_name = 'iDevelopment SMTP Account' , @sequence_number = 1 Go

Handling Security in Database Mail

This section provides some helpful tips when configuring security in Database Mail. At the end of this section is an example T-SQL script that explains the necessary privileges required to use Database Mail.

The DatabaseMailUserRole Role

Database Mail is guarded by the database role DatabaseMailUserRole in the msdb database in order to prevent anyone from sending arbitrary emails. Database users or roles must be created in the msdb database and must also be a member of DatabaseMailUserRole in order to send emails with the exception of sysadmin who has all privileges. For example, the following error message will be thrown if an unauthorized user attempts to send an e-mail message using sp_send_dbmail:

EXEC msdb.dbo.sp_send_dbmail @profile_name = 'iDevelopment.info Profile' , @recipients = 'dba@idevelopment.info' , @subject = 'Automated Test Results (Successful)' , @body = 'The stored procedure finished successfully.' Go Msg 229, Level 14, State 5, Procedure sp_send_dbmail, Line 1 The EXECUTE permission was denied on the object 'sp_send_dbmail', database 'msdb', schema 'dbo'.

In addition to sp_send_dbmail, the following Database Mail stored procedures and views will be inaccessible if the user is not a member of DatabaseMailUserRole:

   sysmail_help_status_sp
   sysmail_delete_mailitems_sp
   sysmail_allitems
   sysmail_event_log
   sysmail_faileditems
   sysmail_mailattachments
   sysmail_sentitems
   sysmail_unsentitems

Profile Security

Execute permissions for sp_send_dbmail default to all members of the DatabaseMailUserRole database role in the msdb database. However, when the user sending the message does not have permission to use the profile for the request, sp_send_dbmail returns an error and does not send the message.

A database user or role must be granted permission to use a Database Mail profile if that profile is private. Since Database Mail enforces security for mail profiles, you must explicitly grant permissions to each msdb database user or role that should have access to a private Database Mail profile. You can grant access to either specific users or to all users in msdb. A private profile restricts access to a specified list of users or roles of the mail-host database (msdb). A public profile, on the other hand, allows any user or role with access to the mail host database (msdb) to send e-mail using that profile. The Database Mail profile used in this guide is a private profile and will therefore require permissions to be granted to a user or role before it can be used to send e-mail.

Create Example User for Database Mail

The following T-SQL creates a new login named [IDEVELOPMENT\AppUser] which will be used in this guide to demonstrate sending messages using Database Mail. AppUser is an Active Directory user who will be authenticated to SQL Server using Windows Authentication. In order to use Database Mail, a new user will be created in the msdb database and granted permission to connect for the AppUser login. As already mentioned in this section, the user must be a member of DatabaseMailUserRole in order to send e-mail messages which can be performed using sp_addrolemember. Next, since the Database Mail profile being used in this guide (iDevelopment.info Profile) is a private profile, the new user will need to be granted permissions before the profile can be used. This is performed using msdb.dbo.sysmail_add_principalprofile_sp. In the example below, I will also use @is_default = 1 when calling msdb.dbo.sysmail_add_principalprofile_sp so this profile will be the default private profile for the new user. Note that each user or role may have only one default profile. Finally, in order to access other database resources used in the guide, a new user will be created in the AppDB and AdventureWorks2012 user databases along with the required permissions.

USE [master] Go -- Create a login account to use Windows Authentication CREATE LOGIN [IDEVELOPMENT\AppUser] FROM WINDOWS WITH DEFAULT_DATABASE = [AppDB]; Go -------------------------------------------------------------- -- Create a user in the [msdb] database USE [msdb] Go CREATE USER [AppUser] FOR LOGIN [IDEVELOPMENT\AppUser]; Go GRANT CONNECT TO [AppUser]; Go -------------------------------------------------------------- USE [msdb] Go -- Add user to the Database Mail role EXEC sp_addrolemember @rolename = 'DatabaseMailUserRole' , @membername = 'AppUser'; Go -- Grants permission for a database user or role -- to use a private Database Mail profile EXEC msdb.dbo.sysmail_add_principalprofile_sp @profile_name = 'iDevelopment.info Profile' , @principal_name = 'AppUser' , @is_default = 1; Go -------------------------------------------------------------- -- Create a user in the [AppDB] database USE [AppDB] Go CREATE USER [AppUser] FOR LOGIN [IDEVELOPMENT\AppUser]; Go GRANT CONNECT TO [AppUser]; Go -------------------------------------------------------------- -- Create a user in the [AdventureWorks2012] database USE [AdventureWorks2012] Go CREATE USER [AppUser] FOR LOGIN [IDEVELOPMENT\AppUser]; Go GRANT CONNECT TO [AppUser]; Go GRANT SELECT ON AdventureWorks2012.Production.WorkOrder TO [AppUser]; Go GRANT SELECT ON AdventureWorks2012.Production.Product TO [AppUser]; Go

Send E-Mail from Database Mail

This section provides several examples of how to send messages using Database Mail. The user sending the messages will be AppUser. This user has the necessary privileges to send messages from Database Mail as described in the section Handling Security in Database Mail.

The examples presented in this section assume that the Database Mail e-mail profile iDevelopment.info Profile has been created as demonstrated earlier in this guide. This profile tells Database Mail which account it should use to send e-mails. In each of the examples presented, I will be providing the @profile parameter to the sp_send_dbmail system procedure to include the iDevelopment.info Profile. When profile is not specified, sp_send_dbmailuses a default profile. If the user sending the e-mail message has a default private profile, Database Mail uses that profile. If the user has no default private profile, sp_send_dbmail uses the default public profile. If there is no default private profile for the user and no default public profile, sp_send_dbmail returns an error.

When sending messages, sp_send_dbmail does not support e-mail messages with no content. To send an e-mail message, you must specify at least one of @body, @query, @file_attachments, or @subject. Otherwise,sp_send_dbmail returns an error.

Send a Simple Text E-Mail Message

This example sends an e-mail message to the Database Administration Team using the e-mail address dba@idevelopment.info. The message has the subject 'Automated Test Results (Successful)'. The body of the message contains the text 'The stored procedure finished successfully.'. Notice that I can be connected to any database and that the sp_send_dbmail stored procedure is in the msdb database.

USE [AppDB] Go EXEC msdb.dbo.sp_send_dbmail @profile_name = 'iDevelopment.info Profile' , @recipients = 'dba@idevelopment.info' , @subject = 'Automated Test Results (Successful)' , @body = 'The stored procedure finished successfully.'; Go

Send an E-Mail Message with the Results of a Query

This example sends an e-mail message to the Database Administration Team using the e-mail address dba@idevelopment.info. The message has the subject 'Work Order Count', and executes a query that shows the number of work orders with a DueDate less than two days after April 30, 2006. Database Mail attaches the result as a text file by using the @attach_query_result_as_file = 1 parameter.

USE [AdventureWorks2012] Go EXEC msdb.dbo.sp_send_dbmail @profile_name = 'iDevelopment.info Profile' , @recipients = 'dba@idevelopment.info' , @subject = 'Work Order Count' , @query = 'SELECT COUNT(*) AS "Number of Work Orders" FROM AdventureWorks2012.Production.WorkOrder WHERE DueDate > ''2006-04-30'' AND DATEDIFF(dd, ''2006-04-30'', DueDate) < 2' , @attach_query_result_as_file = 1 , @query_attachment_filename = 'Work Order Count.txt'; Go

• @query

  The query parameter is of type nvarchar(max) and can contain any valid Transact-SQL statements. Because the query is actually executed in a separate session, local variables in the script callingsp_send_dbmail are not available to the query.

• @attach_query_result_as_file

  Use attach_query_result_as_file to control whether the results of the query will be attached as a file, or included in the body of the e-mail message. attach_query_result_as_file is of type bit, with a default of 0. When the value is 0, the query results are included in the body of the e-mail message, after the contents of the @body parameter. When the value is 1, the results are returned as an attachment. This parameter is only applicable if @query is specified.

• @query_attachment_filename

  query_attachment_filename specifies the file name to use for the result set of the query attachment. query_attachment_filename is of type nvarchar(255), with a default of NULL. This parameter is ignored when attach_query_result is 0. When attach_query_result is 1 and this parameter is NULL, Database Mail creates an arbitrary filename.

• @query_result_width

  query_result_width is the line width, in characters, to use for formatting the results of the query. The query_result_width is of type int, with a default of 256. The value provided must be between 10 and 32767. This parameter is only applicable if @query is specified.

• @query_result_separator

  query_result_separator is the character used to separate columns in the query output. The separator is of type char(1). Defaults to ' ' (space).

• @exclude_query_output

  exclude_query_output specifies whether to return the output of the query execution in the e-mail message. exclude_query_output is bit, with a default of 0. When this parameter is 0, the execution of thesp_send_dbmail stored procedure prints the message returned as the result of the query execution on the console. When this parameter is 1, the execution of the sp_send_dbmail stored procedure does not print any of the query execution messages on the console.

• @append_query_error

  append_query_error specifies whether to send the e-mail when an error returns from the query specified in the @query argument. append_query_error is bit, with a default of 0. When this parameter is 1, Database Mail sends the e-mail message and includes the query error message in the body of the e-mail message. When this parameter is 0, Database Mail does not send the e-mail message, andsp_send_dbmail ends with return code 1, indicating failure.

• @query_no_truncate

  query_no_truncate specifies whether to execute the query with the option that avoids truncation of large variable length data types (varchar(max), nvarchar(max), varbinary(max), xml, text, ntext, image, and user-defined data types). When set, query results do not include column headers. The query_no_truncate value is of type bit. When the value is 0 or not specified, columns in the query truncate to 256 characters. When the value is 1, columns in the query are not truncated. This parameter defaults to 0.

  When used with large amounts of data, the @query_no_truncate option consumes additional resources and can slow server performance.

• @query_result_no_padding

  query_result_no_padding is of type bit. The default is 0. When you set to 1, the query results are not padded, possibly reducing the file size.If you set @query_result_no_padding to 1 and you set the@query_result_width parameter, the @query_result_no_padding parameter overwrites the @query_result_width parameter. In this case no error occurs. If you set the @query_result_no_padding to 1 and you set the @query_no_truncate parameter, an error is raised.

Send an HTML E-Mail Message

This example sends an e-mail message to the Database Administration Team using the e-mail address dba@idevelopment.info. The message has the subject 'Work Order List', and contains an HTML document that shows the work orders with a DueDate less than two days after April 30, 2006. Database Mail sends the message in HTML format using @body_format = 'HTML'. body_format is of type varchar(20) and specifies the format of the message body. Valid values are TEXT (default) and HTML. The default value is NULL which defaults to TEXT. When specified, the headers of the outgoing message are set to indicate that the message body has the specified format.

USE [AdventureWorks2012] Go DECLARE @tableHTML NVARCHAR(MAX); SET @tableHTML = N'<h1>Work Order Report</h1>' + N'<table border="1">' + N'<tr><th>Work Order ID</th><th>Product ID</th>' + N'<th>Name</th><th>Order Qty</th><th>Due Date</th>' + N'<th>Expected Revenue</th></tr>' + CAST ( ( SELECT td = wo.WorkOrderID , '' , td = p.ProductID , '' , td = p.Name , '' , td = wo.OrderQty , '' , td = wo.DueDate , '' , td = (p.ListPrice - p.StandardCost) * wo.OrderQty FROM AdventureWorks2012.Production.WorkOrder AS wo JOIN AdventureWorks2012.Production.Product AS p ON wo.ProductID = p.ProductID WHERE DueDate > '2006-04-30' AND DATEDIFF(dd, '2006-04-30', DueDate) < 2 ORDER BY DueDate ASC , (p.ListPrice - p.StandardCost) * wo.OrderQty DESC FOR XML PATH('tr'), TYPE ) AS NVARCHAR(MAX) ) + N'</table>'; EXEC msdb.dbo.sp_send_dbmail @profile_name = 'iDevelopment.info Profile' , @recipients = 'dba@idevelopment.info' , @subject = 'Work Order List' , @body = @tableHTML , @body_format = 'HTML'; Go

Send an E-Mail Message with File Attachment

Database Mail uses the Microsoft Windows security context of the current user to control access to files. Therefore, users who are authenticated with SQL Server Authentication cannot attach files using @file_attachments. Windows does not allow SQL Server to provide credentials from a remote computer to another remote computer. Therefore, Database Mail may not be able to attach files from a network share in cases where the command is run from a computer other than the computer that SQL Server runs on. Attempting to attach files using file_attachments while authenticated with SQL Server Authentication will result in the following error:

Msg 22051, Level 16, State 1, Line 0 The client connection security context could not be impersonated. Attaching files require an integrated client login

The following example demonstrates how to send multiple file attachments. When sending multiple attachments, each file attachment is separated by a semicolon (;). Space are allowed in the file path so long as the semicolon can delimit each path in the list.

USE [AppDB] Go EXEC msdb.dbo.sp_send_dbmail @profile_name = 'iDevelopment.info Profile' , @recipients = 'dba@idevelopment.info' , @subject = 'Attach Local File' , @body = 'This is a test message to verify that Database Mail can attach multiple files from the local file system.' , @file_attachments = 'U:\MSSQL\Files\linux-logo.png;U:\MSSQL\Files\SQLTestMessage.txt'; Go

By default, Database Mail limits file attachments to 1 MB per file. Changing this limit, as well as modifying other Database Mail system parameters can be performed using the Database Mail Configuration Wizard. To start the Database Mail Configuration Wizard, in Object Explorer, expand the Management folder in SQL Server Management Studio, right-click Database Mail, and select Configure Database Mail. Select View or change system parameters and click Next to continue.

Database Mail Configuration Wizard

The file attachment limitation can be modified using the Maximum File Size (Bytes) system parameter. From the Configure System Parameters screen you can also specify types of attachments that you do not want to send out by including the extensions of the files for the Prohibited Attachment File Extensions system parameter.

Configure System Parameters Screen

Configure Email Notification for SQL Server Agent

After completing the setup and testing of Database Mail, you can optionally select it as your mail system for the SQL Server agent to send e-mail notifications. This can be configured by launching SQL Server Management Studio, right-click the SQL Server Agent node in Object Explorer and select Properties.

SQL Server Agent Properties

Select the Alert System page in the SQL Server Agent Properties dialog. Check the Enable mail profile option and choose Database Mail in the Mail system pull-down menu. Next, verify the correct Mail profile is selected.

SQL Server Agent Properties | Alert System

After configuring the alert system, restart the SQL Server Agent service. If you experience any problems sending e-mail notifications through the SQL Server Agent, check the service account that the agent is running under. If the SQL Server Agent is running with one of the built-in accounts like the Local System account, resources outside the SQL Server machine will be unavailable. This includes SMTP mail servers that are on other machines. If this is the case, change the service account for the SQL Server Agent to a domain account to resolve this issue.

Managing Database Mail

How to Start and Stop Database Mail

Use the msdb.dbo.sysmail_stop_sp system procedure to stop Database Mail. This will stop the Service Broker objects that the external program uses. sp_send_dbmail still accepts mail when Database Mail is stopped usingsysmail_stop_sp.

EXEC msdb.dbo.sysmail_stop_sp; Go

To start Database Mail, use msdb.dbo.sysmail_start_sp.

EXEC msdb.dbo.sysmail_start_sp; Go

The Database Mail External Program is activated when there are e-mail messages to be processed. When there have been no messages to send for the specified time-out period, the program exits. To confirm the Database Mail activation is started, execute the following statement.

EXEC msdb.dbo.sysmail_help_status_sp; Go Status ------- STARTED

If the Database Mail external program is started, check the status of the mail queue with the following statement:

EXEC msdb.dbo.sysmail_help_queue_sp @queue_type = 'mail'; Go queue_type length state last_empty_rowset_time last_activated_time ---------- ------- ------------------- ----------------------- ----------------------- mail 0 RECEIVES_OCCURRING 2013-03-28 03:28:05.517 2013-03-28 03:28:05.517

Possible values for STATE are INACTIVE (queue is inactive), NOTIFIED (queue has been notified receipt to occur), and RECEIVES_OCCURRING (queue is receiving). The mail queue should have the state ofRECEIVES_OCCURRING. The status queue may vary from moment to moment. If the mail queue state is not RECEIVES_OCCURRING, try stopping the queue using sysmail_stop_sp and then starting the queue usingsysmail_start_sp or sending a test email from SSMS.

Archive Database Mail Messages and Event Logs

As mentioned throughout this guide, copies of Database Mail messages and their attachments are retained in msdb tables along with the Database Mail event log. Periodically you might want to reduce the size of the tables and remove messages and events that are no longer needed. The following procedures create a SQL Server Agent job to automate that process.

1. The first procedure creates a job named Archive Database Mail with five steps. The job will be run as SQLServerDBA who is the owner of the Database Mail Archive database (DBMailArchive) and has sysadminprivileges.
2. The first step copies all messages from the Database Mail tables in msdb to a new table in the Database Mail Archive database and named after the previous month in the format DBMailArchive_Messages_<year_month>. For example:

   DBMailArchive.dbo.DBMailArchive_Messages_2013_2
3. The second step copies the attachments related to the messages copied in the first step, from the Database Mail tables in msdb to a new table in the Database Mail Archive database and named after the previous month in the format DBMailArchive_Attachments_<year_month>. For example:

   DBMailArchive.dbo.DBMailArchive_Attachments_2013_2
4. The third step copies the events from the Database Mail event log that are related to the messages copied in the first step, from the Database Mail tables in msdb to a new table in the Database Mail Archive database and named after the previous month in the format DBMailArchive_Event_Log_<year_month>. For example:

   DBMailArchive.dbo.DBMailArchive_Event_Log_2013_2
5. The fourth step deletes the records of the transferred mail items from the Database Mail tables in msdb using the msdb.dbo.sysmail_delete_mailitems_sp stored procedure.
6. The fifth step deletes the events related to the transferred mail items from the Database Mail event log table in msdb using the msdb.dbo.sysmail_delete_log_sp stored procedure.
7. The final procedure schedules the job to run at the start of each month.

For this example, the archive tables will be created in a new database named DBMailArchive owned by SQLServerDBA. The old messages, attachments, and log entries will be moved from the base tables in msdb to the archive tables. When the job completes, an e-mail message will be sent to operators with the status of the run. For production use, you might want to consider more robust error checking. Also, if the archived messages are not required to be stored in a database, they can be exported to a text file, or just deleted.

Create a SQL Server Agent Job

1. In Object Explorer, expand SQL Server Agent, right-click Jobs, and then click New Job.
2. In the New Job dialog box, in the Name box, type Archive Database Mail.
3. In the Owner box, confirm that the job owner is a member of the sysadmin fixed server role. The job owner in this example will be SQLServerDBA who is the owner of the DBMailArchive database and a member of thesysadmin fixed server role.
4. In the Category box, click the Database Maintenance.
5. In the Description box, type Archive Database Mail messages, and then click Steps.

Create a Step to Archive the Database Mail Messages

1. On the Steps page, click New.
2. In the Step name box, type Copy Database Mail Items.
3. In the Type box, select Transact-SQL script (T-SQL).
4. In the Database box, select msdb.
5. In the Command box, type the following statement to create a table named after the previous month, containing rows older than the start of the current month:

   DECLARE @ArchiveDatabaseName NVARCHAR(30) = 'DBMailArchive'; DECLARE @ArchiveSchemaName NVARCHAR(30) = 'dbo'; DECLARE @LastMonth NVARCHAR(12); DECLARE @CopyDate NVARCHAR(20); DECLARE @CreateTable NVARCHAR(250); SET @LastMonth = (SELECT CAST(DATEPART(yyyy,GETDATE()) AS CHAR(4)) + '_' + CAST(DATEPART(mm,GETDATE())-1 AS VARCHAR(2))); SET @CopyDate = (SELECT CAST(CONVERT(CHAR(8), CURRENT_TIMESTAMP - DATEPART(dd,GETDATE()-1), 112) AS DATETIME)); SET @CreateTable = 'SELECT * INTO ' + @ArchiveDatabaseName + '.' + @ArchiveSchemaName + '.[' + @ArchiveDatabaseName + '_Messages_' + @LastMonth + '] FROM sysmail_allitems WHERE send_request_date < ''' + @CopyDate + ''';'; EXEC sp_executesql @CreateTable;

6. Click OK to save the step.

Create a Step to Archive the Database Mail Attachments

1. On the Steps page, click New.
2. In the Step name box, type Copy Database Mail Attachments.
3. In the Type box, select Transact-SQL script (T-SQL).
4. In the Database box, select msdb.
5. In the Command box, type the following statement to create an attachments table named after the previous month, containing the attachments that correspond to the messages transferred in the previous step:

   DECLARE @ArchiveDatabaseName NVARCHAR(30) = 'DBMailArchive'; DECLARE @ArchiveSchemaName NVARCHAR(30) = 'dbo'; DECLARE @LastMonth NVARCHAR(12); DECLARE @CopyDate NVARCHAR(20); DECLARE @CreateTable NVARCHAR(250); SET @LastMonth = (SELECT CAST(DATEPART(yyyy,GETDATE()) AS CHAR(4)) + '_' + CAST(DATEPART(mm,GETDATE())-1 AS VARCHAR(2))); SET @CopyDate = (SELECT CAST(CONVERT(CHAR(8), CURRENT_TIMESTAMP - DATEPART(dd,GETDATE()-1), 112) AS DATETIME)); SET @CreateTable = 'SELECT * INTO ' + @ArchiveDatabaseName + '.' + @ArchiveSchemaName + '.[' + @ArchiveDatabaseName + '_Attachments_' + @LastMonth + '] FROM sysmail_attachments WHERE mailitem_id IN (SELECT DISTINCT mailitem_id FROM ' + @ArchiveDatabaseName + '.' + @ArchiveSchemaName + '.[' + @ArchiveDatabaseName + '_Messages_' + @LastMonth + '] )'; EXEC sp_executesql @CreateTable;

6. Click OK to save the step.

Create a Step to Archive the Database Mail Log

1. On the Steps page, click New.
2. In the Step name box, type Copy Database Mail Log.
3. In the Type box, select Transact-SQL script (T-SQL).
4. In the Database box, select msdb.
5. In the Command box, type the following statement to create a log table named after the previous month, containing the log entries that correspond to the messages transferred in the earlier step:

   DECLARE @ArchiveDatabaseName NVARCHAR(30) = 'DBMailArchive'; DECLARE @ArchiveSchemaName NVARCHAR(30) = 'dbo'; DECLARE @LastMonth NVARCHAR(12); DECLARE @CopyDate NVARCHAR(20); DECLARE @CreateTable NVARCHAR(250); SET @LastMonth = (SELECT CAST(DATEPART(yyyy,GETDATE()) AS CHAR(4)) + '_' + CAST(DATEPART(mm,GETDATE())-1 AS VARCHAR(2))); SET @CopyDate = (SELECT CAST(CONVERT(CHAR(8), CURRENT_TIMESTAMP - DATEPART(dd,GETDATE()-1), 112) AS DATETIME)); SET @CreateTable = 'SELECT * INTO ' + @ArchiveDatabaseName + '.' + @ArchiveSchemaName + '.[' + @ArchiveDatabaseName + '_Event_Log_' + @LastMonth + '] FROM sysmail_event_log WHERE mailitem_id IN (SELECT DISTINCT mailitem_id FROM ' + @ArchiveDatabaseName + '.' + @ArchiveSchemaName + '.[' + @ArchiveDatabaseName + '_Messages_' + @LastMonth + '] )'; EXEC sp_executesql @CreateTable;

6. Click OK to save the step.

Create a Step to Remove the Archived Rows from Database Mail

1. On the Steps page, click New.
2. In the Step name box, type Remove Rows from Database Mail.
3. In the Type box, select Transact-SQL script (T-SQL).
4. In the Database box, select msdb.
5. In the Command box, type the following statement to remove rows older than the current month from the Database Mail tables:

   DECLARE @CopyDate NVARCHAR(20); SET @CopyDate = (SELECT CAST(CONVERT(CHAR(8), CURRENT_TIMESTAMP - DATEPART(dd,GETDATE()-1), 112) AS DATETIME)); EXEC msdb.dbo.sysmail_delete_mailitems_sp @sent_before = @CopyDate;

6. Click OK to save the step.

Create a Step to Remove the Archived Items from Database Mail Event Log

1. On the Steps page, click New.
2. In the Step name box, type Remove Rows from Database Mail Event Log.
3. In the Type box, select Transact-SQL script (T-SQL).
4. In the Database box, select msdb.
5. In the Command box, type the following statement to remove rows older than the current month from the Database Mail event log:

   DECLARE @CopyDate NVARCHAR(20); SET @CopyDate = (SELECT CAST(CONVERT(CHAR(8), CURRENT_TIMESTAMP - DATEPART(dd,GETDATE()-1), 112) AS DATETIME)); EXEC msdb.dbo.sysmail_delete_log_sp @logged_before = @CopyDate;

6. Click OK to save the step.

Schedule the Job to Run at the Start of Each Month

1. In the New Job dialog box, click Schedules.
2. On the Schedules page, click New.
3. In the Name box, type Archive Database Mail.
4. In the Schedule type box, select Recurring.
5. In the Frequency area, select the options to run the job on the first day of every month.
6. In the Daily frequency area, select Occurs once at 3:00:00 AM.
7. Verify that the other options are configured as you wish, and then click OK to save the schedule.
8. Click OK to save the job.