创建 SQL Server 代理作业以存档数据库邮件和事件日志

项目
06/13/2017

数据库邮件及其附件的副本与数据库邮件事件日志一起保存在 msdb 表中。您可能希望定期减小这些表的大小并对不再需要的邮件和事件进行存档。下列过程将创建一个 SQL Server 代理作业，以自动完成上述过程。

开始之前：先决条件、建议、权限
若要使用以下方法存档数据库邮件和日志： SQL Server 代理

开始之前

先决条件

用于存储存档数据的新表可以位于特殊的存档数据库中。另外，还可将行导出到文本文件中。

建议

在生产环境中，可能需要进一步添加错误检查，并在作业失败的情况下向操作员发送电子邮件。

权限

只有 sysadmin 固定服务器角色的成员才能执行本主题中介绍的存储过程。

过程概述

第一个过程创建一个名为“存档数据库邮件”的作业，其中包含下列步骤。
1. 将所有邮件从数据库邮件表中复制到以上一个月份命名的新表中，该表的格式为 DBMailArchive_<year_month>。
2. 将与第一步中复制的邮件相关的附件从数据库邮件表中复制到以上一个月份命名的新表中，该表的格式为 DBMailArchive_Attachments_<year_month>。
3. 将数据库邮件事件日志中与第一步中复制的邮件相关的事件从数据库邮件表中复制到以上一个月份命名的新表中，该表的格式为 DBMailArchive_Log_<year_month>。
4. 从数据库邮件表中删除已传输邮件项的记录。
5. 从数据库邮件事件日志中删除与已传输邮件项相关的事件。
安排定期运行作业。

创建 SQL Server 代理作业

在对象资源管理器中，展开“SQL Server 代理”，右键单击“作业”，然后选择“新建作业”。
在 “新建作业” 对话框的 “名称” 框中，键入 “存档数据库邮件”。
在 “所有者” 框中，确定所有者是 sysadmin 固定服务器角色的成员。
在 “类别” 框中，单击 “数据库维护”。
在 “说明” 框中，键入 “存档数据库邮件”，然后单击 “步骤”。

创建步骤以存档数据库邮件

在 “步骤” 页上，单击 “新建”。
在 “步骤名称” 框中，键入 “复制数据库邮件项”。
在“类型”框中，选择“Transact-SQL 脚本 (T-SQL)”。
在 “数据库” 框中，选择 msdb。

在 “命令” 框中，键入以下语句以创建用上一个月份命名的表，在其中包含早于当前月份的开始日期的行：

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 msdb.dbo.[DBMailArchive_' + @LastMonth + '] FROM sysmail_allitems WHERE send_request_date < ''' + @CopyDate +'''';  
EXEC sp_executesql @CreateTable ;

单击 “确定” 保存步骤。

创建步骤以存档数据库邮件附件

在 “步骤” 页上，单击 “新建”。
在 “步骤名称” 框中，键入 “复制数据库邮件附件”。
在“类型”框中，选择“Transact-SQL 脚本 (T-SQL)”。
在 “数据库” 框中，选择 msdb。

在 “命令” 框中，键入以下语句以创建用上一个月份命名的附件表，在其中包含与上一步中转移的邮件相对应的附件：

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 msdb.dbo.[DBMailArchive_Attachments_' + @LastMonth + '] FROM sysmail_attachments   
 WHERE mailitem_id in (SELECT DISTINCT mailitem_id FROM [DBMailArchive_' + @LastMonth + '] )';  
EXEC sp_executesql @CreateTable ;

单击 “确定” 保存步骤。

创建步骤以存档数据库邮件日志

在 “步骤” 页上，单击 “新建”。
在 “步骤名称” 框中，键入 “复制数据库邮件日志”。
在“类型”框中，选择“Transact-SQL 脚本 (T-SQL)”。
在 “数据库” 框中，选择 msdb。

在 “命令” 框中，键入以下语句以创建用上一个月份命名的日志表，在其中包含与在前面的步骤中传输的邮件相对应的日志项：

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 msdb.dbo.[DBMailArchive_Log_' + @LastMonth + '] FROM sysmail_Event_Log   
 WHERE mailitem_id in (SELECT DISTINCT mailitem_id FROM [DBMailArchive_' + @LastMonth + '] )';  
EXEC sp_executesql @CreateTable ;

单击 “确定” 保存步骤。

创建步骤以从数据库邮件中删除已存档的行

在 “步骤” 页上，单击 “新建”。
在 “步骤名称” 框中，键入 “从数据库邮件中删除行”。
在“类型”框中，选择“Transact-SQL 脚本 (T-SQL)”。
在 “数据库” 框中，选择 msdb。

在 “命令” 框中，键入以下语句以从数据库邮件表中删除早于当前月份的行：

DECLARE @CopyDate nvarchar(20) ;  
SET @CopyDate = (SELECT CAST(CONVERT(char(8), CURRENT_TIMESTAMP- DATEPART(dd,GETDATE()-1), 112) AS datetime)) ;  
EXECUTE msdb.dbo.sysmail_delete_mailitems_sp @sent_before = @CopyDate ;

单击 “确定” 保存步骤。

创建步骤以从数据库邮件事件日志中删除已存档的项

在 “步骤” 页上，单击 “新建”。
在 “步骤名称” 框中，键入 “从数据库邮件事件日志中删除行”。
在“类型”框中，选择“Transact-SQL 脚本 (T-SQL)”。

在 “命令” 框中，键入以下语句以从数据库邮件事件日志中删除早于当前月份的行：

DECLARE @CopyDate nvarchar(20) ;  
SET @CopyDate = (SELECT CAST(CONVERT(char(8), CURRENT_TIMESTAMP- DATEPART(dd,GETDATE()-1), 112) AS datetime)) ;  
EXECUTE msdb.dbo.sysmail_delete_log_sp @logged_before = @CopyDate ;

单击 “确定” 保存步骤。

安排定期运行作业

在 “新建作业” 对话框中，单击 “计划”。
在 “计划” 页上，单击 “新建”。
在 “名称” 框中，键入 “存档数据库邮件”。
在 “计划类型” 框中，选择 “重复执行”。
在 “频率” 区域中，选择相应的选项以便定期运行该作业，比如每月一次。
在“每天频率”区域中，选择“在 <time> 执行一次”。
验证其他选项已按您希望的那样进行了配置，然后单击 “确定” 保存计划。
单击 “确定” 保存作业。

通过