SQL Server 提供的 Database Mail 功能非常好用，設定也很簡單，不過有在使用的人一定會遭遇到運作記錄過大的問題 ( Database Mail 紀錄 )，因此當到了需要清除的時候，就可以參考此篇文章說明進行記錄清除等作業。另一個使用的機會就是當有大量 Database Mail 郵件被產生或因為 SMTP 異常導致失敗時若需要批次刪除這些大量郵件，也可以透過系統內建的預儲程序進行刪除，以下是我的資料庫管理筆記。
我們先談如何清除眾多 Database Mail 紀錄 的問題，透過記錄檔檢視器查看的內容如下圖示：
事實上這些 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 可用的項目有： success、warning、error 和 informational，但事實上是 informational 是錯誤的，應該要設定為 information 才對，這應該是文件上的錯誤吧！如下圖示：
另一種刪除記錄的方式是依據記錄的日期來決定刪除的歷史資料，如下 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 系統預儲程序刪除這些郵件。
以下是常用的語法：
-- 刪除【已發送】狀態的郵件
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 才能執行喔！
相關連結