共用方式為

建立 SQL Server Agent 作業以封存 Database Mail 訊息及事件記錄檔

適用於：SQL ServerAzure SQL 受控執行個體

Database Mail 訊息及其附件的副本會與 Database Mail 事件記錄檔一起保留在數據表中 msdb 。 您可能需要定期減少資料表的大小，並封存不再需要的訊息和事件。

下列步驟可建立 SQL Server Agent 的作業以自動化過程。

必要條件

若要在 SQL Server 實例上執行 T-SQL 命令，請使用 SQL Server Management Studio （SSMS）、Visual Studio Code、sqlcmd 或您最愛的 T-SQL 查詢工具的 MSSQL 擴充功能。

建議

如果此封存作業失敗，請考慮檢查錯誤並監視此作業，以將電子郵件訊息傳送給作員。

或者，您可以將封存的 Database Mail 數據移至 外部 msdb的自定義封存資料庫，或從 SQL Server 匯出。

權限

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

建立封存資料庫郵件作業

第一個程序會建立一個名稱為「Archive Database Mail」的作業，並包含下列步驟。

  1. 將所有訊息從 Database Mail 資料表複製到新資料表中，該資料表以上一個月份的名稱命名，格式為 DBMailArchive__<year_month>。

  2. 將與第一步中複製的郵件相關的附件，從 Database Mail 資料表複製到一個以上個月命名且格式為 DBMailArchive_Attachments_<year_month> 的新資料表中。

  3. 將與第一步中複製的訊息相關的事件從 Database Mail 事件記錄以及數據表中複製，並將這些事件轉移到一個以上個月命名的新數據表中，格式為DBMailArchive_Log_<year_month>。

  4. 刪除 Database Mail 資料表中已轉移郵件項目的記錄。

  5. 刪除 Database Mail 事件記錄檔中已轉移郵件項目的相關事件。

  6. 排程作業以定期執行。

建立 SQL Server Agent 作業

下列步驟使用 SQL Server Management Studio (SSMS)。 在 aka.ms/ssms 下載最新版 SSMS。

  1. 連線到 SQL Server 執行個體。

  2. 在 [物件總管] 中，展開 [SQL Server Agent]，以滑鼠右鍵按兩下 [ 作業]，然後選取 [ 新增作業]。

  3. 在 [新增作業] 對話方塊的 [名稱] 方塊中，輸入 封存資料庫郵件。

  4. 在 [擁有者] 方塊中，確認該位擁有者是 系統管理員 (sysadmin) 固定伺服器角色的成員。

  5. 在 [ 類別] 方塊 中，選取 [資料庫維護]。

  6. 在 [ 描述] 方塊中，輸入 封存 Database Mail 訊息，然後選取 [ 步驟]。

建立作業步驟以封存 Database Mail 訊息

  1. 在 [ 步驟] 頁面上，選取 [ 新增]。

  2. 在 [步驟名稱] 方塊中，輸入 複製 Database Mail 項目。

  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. 選取 [確定 ] 以儲存步驟。

建立作業步驟以封存 Database Mail 附件

  1. 在 [ 步驟] 頁面上，選取 [ 新增]。

  2. 在 [步驟名稱] 方塊中，輸入 複製 Database Mail 附加檔案。

  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. 選取 [確定 ] 以儲存步驟。

建立作業步驟以封存 Database Mail 記錄檔

  1. 在 [ 步驟] 頁面上，選取 [ 新增]。

  2. 在 [步驟名稱] 方塊中，輸入 複製 Database Mail 記錄。

  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. 選取 [確定 ] 以儲存步驟。

建立作業步驟以從 Database Mail 移除封存的數據列

  1. 在 [ 步驟] 頁面上，選取 [ 新增]。

  2. 在 [步驟名稱] 方塊中，輸入 從 Database Mail 移除資料列。

  3. 在 [類型] 方塊中，選取 [Transact-SQL 指令碼 (T-SQL)] 。

  4. 在 [ 資料庫] 方塊中，選取 msdb。

  5. 在 [命令] 方塊中，輸入下列陳述式，從 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 ;

  6. 選取 [確定 ] 以儲存步驟。

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

  1. 在 [ 步驟] 頁面上，選取 [ 新增]。

  2. 在 [ 步驟名稱] 方塊中，輸入 從Database Mail事件記錄檔移除資料列。

  3. 在 [類型] 方塊中，選取 [Transact-SQL 指令碼 (T-SQL)] 。

  4. 在 [命令] 方塊中，輸入下列陳述式，從 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 ;

  5. 選取 [確定 ] 以儲存步驟。

將工作排程為定期執行

  1. 在 [ 新增作業] 對話框中，選取 [ 排程]。

  2. 在 [ 排程] 頁面上，選取 [ 新增]。

  3. 在 [名稱] 方塊中，輸入 封存 Database Mail。

  4. 在 [排程類型] 方塊中，選取 [重複執行] 。

  5. 在 [頻率] 區域中，選取定期執行作業的選項 (例如一個月一次)。

  6. 在 [每日頻率] 區域中，選取 [只發生一次於 時間<]。

  7. 確認已視需要設定其他選項，然後選取 [ 確定 ] 以儲存排程。

  8. 選取 [確定] 以儲存作業。

相關內容

  • Last updated on