Introduction
- 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
Verify SQL Server Agent is Running
USE master
EXEC xp_servicecontrol
N'QueryState',
N'SQLServerAGENT';
Current Service State
USE master
SELECT
is_broker_enabled
is_broker_enabled
ALTER DATABASE msdb SET ENABLE_BROKER WITH ROLLBACK IMMEDIATE;
- sp_configure Stored Procedure
To
enable Database Mail using the sp_configure stored procedure, run
the following.
USE master
EXEC sp_configure
'show advanced options',
1
Configuration option 'show advanced options' changed from 0 to
1. Run the RECONFIGURE statement to install.
RECONFIGURE
Command(s) completed successfully.
EXEC sp_configure
'Database Mail XPs',
1
Configuration option 'Database Mail XPs' changed from 0 to 1.
Run the RECONFIGURE statement to install.
RECONFIGURE
Command(s) completed successfully.
EXEC sp_configure
'show advanced options',
0
Configuration option 'show advanced options' changed from 1 to
0. Run the RECONFIGURE statement to install.
RECONFIGURE
- 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.
USE master
USE master
USE master
 |
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 Ok button.
 | |
Enable Database Mail Feature
| |
- 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 you to 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.
 | |
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 of msdb.dbo.sysmail_event_log.
SELECT * FROM msdb.dbo.sysmail_event_log;
Go
 | |
Verify Email was Sent and Received
| |
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:
- Create one or more Database Mail accounts for the
profile using the msdb.dbo.sysmail_add_account_sp stored procedure.
- 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.
- For each account, execute msdb.dbo.sysmail_add_profileaccount_sp to add the account to the profile.
- 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
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 msdbdatabase 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 usingmsdb.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 AdventureWorks2008R2user 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 [AdventureWorks2008R2] database
USE [AdventureWorks2008R2]
Go
CREATE USER
[AppUser] FOR LOGIN [IDEVELOPMENT\AppUser];
Go
GRANT CONNECT TO [AppUser];
Go
GRANT SELECT ON
AdventureWorks2008R2.Production.WorkOrder TO
[AppUser];
Go
GRANT SELECT ON
AdventureWorks2008R2.Production.Product TO
[AppUser];
Go
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_dbmail uses 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 AdventureWorks2008R2
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
AdventureWorks2008R2.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
calling sp_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 the sp_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 thesp_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, and sp_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.
|
- @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 AdventureWorks2008R2
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
AdventureWorks2008R2.Production.WorkOrder AS wo
JOIN
AdventureWorks2008R2.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.
 | |
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.
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 using sysmail_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 2011-04-19
19:41:20.323 2011-04-19 19:40:17.787
The mail queue should
have the state of RECEIVES_OCCURRING. The status queue may vary from moment to
moment. If the mail queue state is not RECEIVES_OCCURRING, try stopping the queue usingsysmail_stop_sp and then starting the queue using sysmail_start_sp.
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.
- 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 hassysadmin privileges.
- 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 formatDBMailArchive_Messages_<year_month>. For example:
DBMailArchive.dbo.DBMailArchive_Messages_2011_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_2011_3
- 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_2011_3
- 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.
- 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.
- 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 msdbto 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
- In Object Explorer, expand SQL Server Agent,
right-click Jobs,
and then click New Job.
- In the New Job dialog
box, in the Name box, type Archive Database Mail.
- 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 the sysadmin fixed
server role.
- In the Category box, click
the Database Maintenance.
- In the Description box, type Archive Database Mail messages, and then click Steps.
Create a Step to Archive the Database Mail Messages
- On the Steps page,
click New.
- In the Step name box, type Copy Database Mail Items.
- In the Type box,
select Transact-SQL script (T-SQL).
- In the Database box,
select msdb.
- 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;
- Click OK to save the step.
Create a Step to Archive the Database Mail Attachments
- On the Steps page,
click New.
- In the Step name box, type Copy Database Mail Attachments.
- In the Type box,
select Transact-SQL script (T-SQL).
- In the Database box,
select msdb.
- 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;
- Click OK to save the step.
Create a Step to Archive the Database Mail Log
- On the Steps page, click New.
- In the Step name box, type Copy Database Mail Log.
- In the Type box,
select Transact-SQL script (T-SQL).
- In the Database box,
select msdb.
- 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;
- Click OK to save the step.
Create a Step to Remove the Archived Rows from Database Mail
- On the Steps page,
click New.
- In the Step name box, type Remove Rows from Database Mail.
- In the Type box,
select Transact-SQL script (T-SQL).
- In the Database box, select msdb.
- 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;
- Click OK to save the step.
Create a Step to Remove the Archived Items from Database Mail
Event Log
- On the Steps page,
click New.
- In the Step name box, type Remove Rows from Database Mail Event Log.
- In the Type box,
select Transact-SQL script (T-SQL).
- In the Database box,
select msdb.
- 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;
- Click OK to save the step.
Schedule the Job to Run at the Start of Each Month
- In the New Job dialog
box, click Schedules.
- On the Schedules page,
click New.
- In the Name box, type Archive Database Mail.
- In the Schedule type box,
select Recurring.
- In the Frequency area,
select the options to run the job on the first day of every month.
- In the Daily frequency area,
select Occurs once at 3:00:00 AM.
- Verify that the other options are configured as you
wish, and then click OK to save the schedule.
- Click OK to save the job.
No comments:
Post a Comment