The Will Will Web

記載著 Will 在網路世界的學習心得與技術分享

如何清除 SQL Server 的 Database Mail 紀錄與發送失敗郵件

SQL Server 提供的 Database Mail 功能非常好用,設定也很簡單,不過有在使用的人一定會遭遇到運作記錄過大的問題 ( Database Mail 紀錄 ),因此當到了需要清除的時候,就可以參考此篇文章說明進行記錄清除等作業。另一個使用的機會就是當有大量 Database Mail 郵件被產生或因為 SMTP 異常導致失敗時若需要批次刪除這些大量郵件,也可以透過系統內建的預儲程序進行刪除,以下是我的資料庫管理筆記。

我們先談如何清除眾多 Database Mail 紀錄 的問題,透過記錄檔檢視器查看的內容如下圖示:

alt

事實上這些 Database Mail 記錄檔是儲存在 msdb 系統資料庫中,由於這是系統資料庫,因此並不建議直接對系統資料表做修改,還是建議使用 SQL Server 預設提供的系統預儲程序 sysmail_delete_log_sp來清除記錄資料,使用的方式如下:

-- 刪除所有紀錄
exec sysmail_delete_log_sp
-- 刪除執行成功的紀錄
exec sysmail_delete_log_sp @event_type = 'success'
-- 刪除警示的紀錄
exec sysmail_delete_log_sp @event_type = 'warning'
-- 刪除錯誤的紀錄
exec sysmail_delete_log_sp @event_type = 'error'
-- 刪除資訊的紀錄 (這一個種類的紀錄應該會最多)
exec sysmail_delete_log_sp @event_type = 'information'

注意事項

在 MSDN 上關於 sysmail_delete_log_sp (Transact-SQL) 的說明有提到 @event_type 可用的項目有: successwarningerrorinformational,但事實上是 informational 是錯誤的,應該要設定為 information 才對,這應該是文件上的錯誤吧!如下圖示:

alt

 

另一種刪除記錄的方式是依據記錄的日期來決定刪除的歷史資料,如下 T-SQL 範例:

-- 刪除特定日期之前的紀錄
exec sysmail_delete_log_sp @logged_before = '2010-12-17'

-- 刪除 7 天以前的歷史紀錄
DECLARE @DeleteBeforeDate DateTime 
SELECT @DeleteBeforeDate = DATEADD(d,-7, GETDATE())
EXEC sysmail_delete_log_sp @logged_before = @DeleteBeforeDate

 

接著我們來談如何清除無效未發送的 Database Mail 郵件 問題,像我們前陣子客戶的機房環境的 Firewall 設定錯誤,導致機房內的 SMTP 伺服器連不上,以致於 Database Mail 產生了一堆記錄,而這些曾經發但發送不出去的郵件就會全部累積在 msdb 資料庫裡,如果這些郵件不是挺重要的話,也可以利用系統內建的 sysmail_delete_mailitems_sp 系統預儲程序刪除這些郵件。

alt

以下是常用的語法:

-- 刪除【已發送】狀態的郵件
exec sysmail_delete_mailitems_sp @sent_status='sent'
-- 刪除【未發送】狀態的郵件
exec sysmail_delete_mailitems_sp @sent_status='unsent'
-- 刪除【重試中】狀態的郵件
exec sysmail_delete_mailitems_sp @sent_status='retrying'
-- 刪除【發送失敗】狀態的郵件 (這種郵件也不會再重發,會一直積在系統裡)
exec sysmail_delete_mailitems_sp @sent_status='failed'

-- 刪除 7 天以前的歷史郵件
DECLARE @DeleteBeforeDate DateTime 
SELECT @DeleteBeforeDate = DATEADD(d,-7, GETDATE())
EXEC sysmail_delete_mailitems_sp @sent_before = @DeleteBeforeDate

以下是查詢目前還有多少 Database Mail 郵件的方式:

-- 查詢目前所有的 Database Mail 郵件項目
SELECT * FROM [msdb].[dbo].[sysmail_mailitems] ORDER BY [send_request_date] DESC

 

注意事項

執行這些系統預儲程序時,請記得要先切換目前資料庫到 msdb 才能執行喔!

alt

相關連結