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

适用于：SQL ServerAzure SQL 托管实例

数据库邮件及其附件的副本与数据库邮件事件日志一起保留在 msdb 表中。 您可能希望定期减小这些表的大小并对不再需要的邮件和事件进行存档。

下列过程将创建一个 SQL Server 代理作业，以自动完成上述过程。

先决条件

若要在 SQL Server 实例上运行 T-SQL 命令，请使用 SQL Server Management Studio （SSMS）、 Visual Studio Code 的 MSSQL 扩展、 sqlcmd 或你喜欢的 T-SQL 查询工具。

建议

考虑进行错误检查，并监视这个作业，以便在这个归档作业失败时向操作员发送电子邮件。

（可选）可以将存档的数据库邮件数据移到外部 msdb的自定义存档数据库中，或从 SQL Server 导出它们。

权限

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

创建存档数据库邮件作业

第一个过程创建一个名为“存档数据库邮件”的作业，其中包含下列步骤。

1. 将数据库邮件表中的所有邮件复制到一个以上个月命名的新表，其格式为 DBMailArchive__<year_month>。

2. 将与第一步中复制的邮件相关的附件从数据库邮件的表格中复制到一个以上个月命名的新表，格式为DBMailArchive_Attachments_<year_month>。

3. 从数据库邮件事件日志中复制与第一步中复制的邮件相关的事件，将它们从“数据库邮件”表复制到一个新的表中，该表以上个月的月份为名，格式为DBMailArchive_Log_<year_month>。

4. 从数据库邮件表中删除已传输邮件项的记录。

5. 从数据库邮件事件日志中删除与已传输邮件项相关的事件。

6. 安排定期运行作业。

创建 SQL Server 代理作业

以下步骤使用 SQL Server Management Studio (SSMS)。 在 aka.ms/ssms 下载最新版本的 SSMS。

1. 连接到 SQL Server 实例。

2. 在对象资源管理器中，展开 SQL Server 代理，右键单击“ 作业”，然后选择“ 新建作业”。

3. 在 “新建作业” 对话框的 “名称” 框中，键入 “存档数据库邮件”。

4. 在 “所有者” 框中，确定所有者是 sysadmin 固定服务器角色的成员。

5. 在 “类别” 框中，选择 “数据库维护”。

6. 在 “说明 ”框中，键入 “存档数据库邮件”，然后选择“ 步骤”。

创建用于存档数据库邮件的作业步骤

1. 在“ 步骤 ”页上，选择“ 新建”。

2. 在 “步骤名称” 框中，键入 “复制数据库邮件项”。

3. 在“类型”框中，选择“Transact-SQL 脚本 (T-SQL)”。

4. 在 “数据库 ”框中，选择 msdb。

5. 在 “命令 ”框中，键入以下 T-SQL 语句以创建一个以上月命名的表，其中包含早于当前月份开头的行。

   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 ;  
   

6. 选择 “确定 ”保存步骤。

创建用于存档数据库邮件附件的作业步骤

1. 在“ 步骤 ”页上，选择“ 新建”。

2. 在 “步骤名称” 框中，键入 “复制数据库邮件附件”。

3. 在“类型”框中，选择“Transact-SQL 脚本 (T-SQL)”。

4. 在 “数据库 ”框中，选择 msdb。

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

   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 ;  
   

6. 选择 “确定 ”保存步骤。

创建用于存档数据库邮件日志的作业步骤

1. 在“ 步骤 ”页上，选择“ 新建”。

2. 在 “步骤名称” 框中，键入 “复制数据库邮件日志”。

3. 在“类型”框中，选择“Transact-SQL 脚本 (T-SQL)”。

4. 在 “数据库 ”框中，选择 msdb。

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

   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 ;  
   

6. 选择 “确定 ”保存步骤。

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

1. 在“ 步骤 ”页上，选择“ 新建”。

2. 在 “步骤名称” 框中，键入 “从数据库邮件中删除行”。

3. 在“类型”框中，选择“Transact-SQL 脚本 (T-SQL)”。

4. 在 “数据库 ”框中，选择 msdb。

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

   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 ;  
   

6. 选择 “确定 ”保存步骤。

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

1. 在“ 步骤 ”页上，选择“ 新建”。

2. 在 “步骤名称 ”框中，键入 “从数据库邮件事件日志中删除行”。

3. 在“类型”框中，选择“Transact-SQL 脚本 (T-SQL)”。

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

   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 ;  
   

5. 选择 “确定 ”保存步骤。

调度作业以定期运行

1. 在“ 新建作业 ”对话框中，选择“ 计划”。

2. 在“ 计划 ”页上，选择“ 新建”。

3. 在 “名称” 框中，键入 “存档数据库邮件”。

4. 在 “计划类型” 框中，选择 “重复执行”。

5. 在 “频率” 区域中，选择相应的选项以便定期运行该作业，比如每月一次。

6. 在“每天频率”区域中，选择“在 <time> 执行一次”。

7. 验证是否按需要配置了其他选项，然后选择“ 确定 ”以保存计划。

8. 选择 “确定” 以保存任务。

相关内容

• 常规数据库邮件故障排除步骤