共用方式為

  • 發行項

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

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

  1. 第一個程序會建立一個名稱為「封存 Database Mail」的作業，此作業中包含四個步驟。

  2. 第一步將 Database Mail 資料表的所有訊息複製到新的資料表，新資料表名稱的格式是 DBMailArchive_<year_month>，接續上一個月份的名稱命名。

  3. 第二步將第一步複製之訊息的相關附件，從 Database Mail 複製到新的資料表，新資料表名稱的格式是 DBMailArchive_Attachments_<year_month>，接續上一個月份的名稱命名。

  4. 第三步從 Database Mail 事件記錄檔，將第一步複製之訊息相關的事件複製到新的資料表，新資料表名稱的格式是 DBMailArchive_Log_<year_month>，接續上一個月份的名稱命名。

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

  6. 第五步刪除 Database Mail 事件記錄檔中，已轉移之郵件項目的相關事件。

  7. 最後一步將作業排程在每月月初執行。

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

建立 SQL Server Agent 作業

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

  2. 在 [新增作業] 對話方塊的 [名稱] 方塊中，鍵入封存 Database Mail。

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

  4. 在 [類別目錄] 方塊中，按一下 [資料庫維護]。

  5. 在 [描述] 方塊中，鍵入封存 Database Mail 訊息，然後按一下 [步驟]。

建立封存 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_' + @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. 在 [資料庫] 方塊中，選取 [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_log_sp @logged_before = @CopyDate ;

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

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

  1. 在 [新增作業] 對話方塊中，按一下 [排程]。

  2. 在 [排程] 頁面上，按一下 [新增]。

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

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

  5. 在 [頻率] 區域中，選取選項以在每個月的第一天執行作業。

  6. 在 [每日頻率] 區域中，選取 [執行一次於上午 3:00:00]。

  7. 視需要設定其他選項，然後按一下 [確定] 儲存排程。

  8. 按一下 [確定] 以儲存作業。

安全性

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