發行項
04/02/2012

如何：建立 SQL Server Agent 作業以封存 Database Mail 訊息和事件記錄檔

Database Mail 訊息的副本及其附件會隨著 Database Mail 事件記錄檔一起保留在 msdb 資料表。您可能需要定期減少資料表的大小，並移除不再使用的訊息和事件。下列程序可建立 SQL Server Agent 作業以便自動執行程序。

第一個程序會建立一個名稱為「封存 Database Mail」的作業，此作業中包含四個步驟。
第一步將 Database Mail 資料表的所有訊息複製到新的資料表，新資料表名稱的格式是 DBMailArchive_<year_month>，接續上一個月份的名稱命名。
第二步將第一步複製之訊息的相關附件，從 Database Mail 複製到新的資料表，新資料表名稱的格式是 DBMailArchive_Attachments_<year_month>，接續上一個月份的名稱命名。
第三步從 Database Mail 事件記錄檔，將第一步複製之訊息相關的事件複製到新的資料表，新資料表名稱的格式是 DBMailArchive_Log_<year_month>，接續上一個月份的名稱命名。
第四步刪除 Database Mail 資料表中已轉移的郵件項目記錄。
第五步刪除 Database Mail 事件記錄檔中，已轉移之郵件項目的相關事件。
最後一步將作業排程在每月月初執行。

在此範例中，封存資料表會建立在 msdb 資料庫中。若要減少 msdb 資料庫的大小，可將新的資料表放在特殊的封存資料庫，或將資料列匯出到文字檔，或者將它刪除。此範例只將資料列移動到 msdb 資料庫的新資料表。在實際執行環境中，您也可以加入其他錯誤檢查，並在作業失敗時傳送電子郵件訊息給操作員。

建立 SQL Server Agent 作業

在 [物件總管] 中，展開 [SQL Server Agent]，以滑鼠右鍵按一下 [作業]，然後按一下 [新增作業]。
在 [新增作業] 對話方塊的 [名稱] 方塊中，鍵入封存 Database Mail。
在 [擁有者] 方塊中，確認該位擁有者是系統管理員 (sysadmin) 固定伺服器角色的成員。
在 [類別目錄] 方塊中，按一下 [資料庫維護]。
在 [描述] 方塊中，鍵入封存 Database Mail 訊息，然後按一下 [步驟]。

建立封存 Database Mail 訊息的步驟

在 [步驟] 頁面上，按一下 [新增]。
在 [步驟名稱] 方塊中，鍵入複製 Database Mail 項目。
在 [類型] 方塊中，選取 [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 ;

按一下 [確定] 以儲存步驟。

建立封存 Database Mail 附加檔案的步驟

在 [步驟] 頁面上，按一下 [新增]。
在 [步驟名稱] 方塊中，鍵入複製 Database Mail 附加檔案。
在 [類型] 方塊中，選取 [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 ;

按一下 [確定] 以儲存步驟。

建立封存 Database Mail 記錄的步驟

在 [步驟] 頁面上，按一下 [新增]。
在 [步驟名稱] 方塊中，鍵入複製 Database Mail 記錄。
在 [類型] 方塊中，選取 [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 ;

按一下 [確定] 以儲存步驟。

建立從 Database Mail 移除封存資料列的步驟

在 [步驟] 頁面上，按一下 [新增]。
在 [步驟名稱] 方塊中，鍵入從 Database Mail 移除資料列。
在 [類型] 方塊中，選取 [Transact-SQL 指令碼 (T-SQL)]。
在 [資料庫] 方塊中，選取 [msdb]。

在 [命令] 方塊中，鍵入以下陳述式，從 Database Mail 資料表移除這個月之前的資料列：

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 ;

按一下 [確定] 以儲存步驟。

建立從 Database Mail 事件記錄檔移除封存項目的步驟

在 [步驟] 頁面上，按一下 [新增]。
在 [步驟名稱] 方塊中，鍵入從 Database Mail 事件記錄檔移除資料列。
在 [類型] 方塊中，選取 [Transact-SQL 指令碼 (T-SQL)]。
在 [資料庫] 方塊中，選取 [msdb]。

在 [命令] 方塊中，鍵入以下陳述式，從 Database Mail 事件記錄檔移除這個月之前的資料列：

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 ;

按一下 [確定] 以儲存步驟。

將作業排程在每個月月初執行

在 [新增作業] 對話方塊中，按一下 [排程]。
在 [排程] 頁面上，按一下 [新增]。
在 [名稱] 方塊中，鍵入封存 Database Mail。
在 [排程類型] 方塊中，選取 [重複執行]。
在 [頻率] 區域中，選取選項以在每個月的第一天執行作業。
在 [每日頻率] 區域中，選取 [執行一次於上午 3:00:00]。
視需要設定其他選項，然後按一下 [確定] 儲存排程。
按一下 [確定] 以儲存作業。

安全性

您必須是系統管理員 (sysadmin) 固定伺服器角色的成員，才能執行此主題中所描述的預存程序。

請參閱

參考

Database Mail 檢視 (Transact-SQL)

Database Mail 和 SQL Mail 預存程序 (Transact-SQL)

Creating SQL Server Agent Jobs

概念

Database Mail