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

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

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

![alt](https://stwillblogassets.blob.core.windows.net/files/images/external/stwillblogassets.blob.core.windows.net/83488fb99e01271d8de7-image_397.png "title")

事實上這些 Database Mail 記錄檔是儲存在 **msdb** 系統資料庫中，由於這是系統資料庫，因此並不建議直接對系統資料表做修改，還是建議使用 SQL Server 預設提供的系統預儲程序 **[sysmail\_delete\_log\_sp](http://msdn.microsoft.com/zh-tw/library/ms189810.aspx)**來清除記錄資料，使用的方式如下：

```
-- 刪除所有紀錄
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)](http://msdn.microsoft.com/zh-tw/library/ms189810.aspx) 的說明有提到 **@event\_type** 可用的項目有： **success**、**warning**、**error** 和 **informational**，但事實上是 **informational** 是錯誤的，應該要設定為 **information** 才對，這應該是文件上的錯誤吧！如下圖示：

![alt](https://stwillblogassets.blob.core.windows.net/files/images/external/stwillblogassets.blob.core.windows.net/0c5f481de213b0f8b942-image_398.png "title")

另一種刪除記錄的方式是依據記錄的日期來決定刪除的歷史資料，如下 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**](http://msdn.microsoft.com/zh-tw/library/ms190293.aspx) 系統預儲程序刪除這些郵件。

![alt](https://stwillblogassets.blob.core.windows.net/files/images/external/stwillblogassets.blob.core.windows.net/c07fefa8d2e1fd5a2d45-image_399.png "title")

以下是常用的語法：

```
-- 刪除【已發送】狀態的郵件
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](https://stwillblogassets.blob.core.windows.net/files/images/external/stwillblogassets.blob.core.windows.net/fb53e0ea4443ee7a564e-image_400.png "title")

**相關連結**

-   [sysmail\_delete\_log\_sp (Transact-SQL)](http://msdn.microsoft.com/zh-tw/library/ms189810.aspx)
-   [sysmail\_delete\_mailitems\_sp (Transact-SQL)](http://msdn.microsoft.com/zh-tw/library/ms190293.aspx)
-   [SQL Server Database Mail Cleanup Procedures](http://www.mssqltips.com/tip.asp?tip=1732)
-   [SQL 2005 Database Mail](https://dotblogs.com.tw/alonstar/2008/08/07/4832)