通过

  • 项目

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

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

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

  2. 第一个步骤是将所有邮件从数据库邮件表中复制到一个按 DBMailArchive_<year_month> 格式、用上一个月份命名的新表中。

  3. 第二个步骤是将与第一个步骤中复制的邮件相关的附件从数据库邮件表中复制到按 DBMailArchive_Attachments_<year_month> 格式、用上一个月份命名的新表中。

  4. 第三个步骤是将数据库邮件事件日志中与第一个步骤中复制的邮件相关的事件从数据库邮件表中复制到按 DBMailArchive_Log_<year_month> 格式、用上一个月份命名的新表中。

  5. 第四个步骤是从数据库邮件表中删除已传输邮件项的记录。

  6. 第五个步骤是从数据库邮件事件日志中删除与已传输邮件项相关的事件。

  7. 最后一个过程是计划在每月的开始运行该作业。

对于此示例，将在 msdb 数据库中创建存档表。若要减小 msdb 数据库的大小，可以将新表放到一个特殊的存档数据库中，也可以将行导出到文本文件中，还可以直接删除行。此示例仅将行移动到 msdb 数据库的新表中。若要在生产环境中使用，可能需要进一步添加错误检查，并在作业失败的情况下向操作员发送电子邮件。

创建 SQL Server 代理作业

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

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

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

  4. 在**“类别”框中，单击“数据库维护”**。

  5. 在**“说明”框中，键入“存档数据库邮件”，然后单击“步骤”**。

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

  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_' + @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. 在**“数据库”**框中，选择 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_log_sp @logged_before = @CopyDate ;

  6. 单击**“确定”**保存步骤。

计划在每月的开始运行作业

  1. 在**“新建作业”对话框中，单击“计划”**。

  2. 在**“计划”页上，单击“新建”**。

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

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

  5. 在**“频率”**区域中，选择相应的选项以便在每月的第一天运行该作业。

  6. 在**“每天频率”区域中，选择“在 3:00:00 AM 执行一次”**。

  7. 验证其他选项已按您希望的那样进行了配置，然后单击**“确定”**保存计划。

  8. 单击**“确定”**保存作业。

安全性

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