建立 SQL Server Agent 作業以封存 Database Mail 訊息及事件記錄檔
Database Mail 訊息的副本及其附件會隨著 Database Mail 事件記錄檔一起保留在 msdb 資料表。 您可能需要定期減少資料表的大小,並封存不再需要的訊息和事件。 下列程序可建立 SQL Server Agent 作業以便自動執行程序。
開始之前:必要條件、建議、權限
使用下列項目,封存 Database Mail 訊息和記錄檔:SQL Server Agent
開始之前
必要條件
要儲存封存資料的新資料表可能位在特殊的封存資料庫中。 資料列也可以匯出至文字檔。
回到頁首
建議
在實際執行環境中,您可能會想要加入其他錯誤檢查,並在作業失敗時傳送電子郵件訊息給操作員。
回到頁首
權限
您必須是系統管理員 (sysadmin) 固定伺服器角色的成員,才能執行此主題中所描述的預存程序。
回到頁首
處理序的概觀
第一個程序會建立一個名稱為「封存 Database Mail」的作業,此作業包含下列步驟。
將 Database Mail 資料表的所有訊息複製到新的資料表,新資料表名稱的格式是 DBMailArchive_<year_month>,接續上一個月份的名稱命名。
將第一個步驟複製之訊息的相關附件,從 Database Mail 資料表複製到新的資料表,新資料表名稱的格式是 DBMailArchive_Attachments_<year_month>,接續上一個月份的名稱命名。
將 Database Mail 事件記錄檔中第一個步驟複製之訊息的相關事件,從 Database Mail 資料表複製到新的資料表,新資料表名稱的格式是 DBMailArchive_Log_<year_month>,接續上一個月份的名稱命名。
刪除 Database Mail 資料表中已轉移郵件項目的記錄。
刪除 Database Mail 事件記錄檔中已轉移郵件項目的相關事件。
排程定期執行作業。
回到頁首
建立 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)]。
在 [命令] 方塊中,輸入下列陳述式,從 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]。
在 [排程類型] 方塊中,選取 [重複執行]。
在 [頻率] 區域中,選取定期執行作業的選項 (例如一個月一次)。
在 [每日頻率] 區域中,選取 [執行一次於 <時間>]。
視需要設定其他選項,然後按一下 [確定] 儲存排程。
按一下 [確定] 以儲存作業。
概觀