Database Mail相關Tables & SP

--列出Database Mail設定檔
SELECT * FROM msdb.dbo.sysmail_profile

--列出Database Mail帳戶
SELECT * FROM msdb.dbo.sysmail_account

--列出設定檔對應的帳戶
SELECT * FROM msdb.dbo.sysmail_profileaccount

--列出主要的設定檔
SELECT * FROM msdb.dbo.sysmail_principalprofile

--Mail Server相關設定
SELECT * FROM msdb.dbo.sysmail_server
SELECT * FROM msdb.dbo.sysmail_servertype

--列出Database Mail 帳戶的相關資訊(密碼除外)。
EXECUTE msdb.dbo.sysmail_help_account_sp

--列出一或多個郵件設定檔的相關資訊。
EXECUTE msdb.dbo.sysmail_help_profile_sp

--列出一或多個Database Mail 設定檔的相關帳戶。
EXECUTE msdb.dbo.sysmail_help_profileaccount_sp

--列出Database Mail系統參數
SELECT * FROM msdb.dbo.sysmail_configuration

--查看所有訊息的狀態
SELECT * FROM msdb.dbo.sysmail_allitems ORDER BY send_request_date DESC

--查看傳送成功的訊息
SELECT * FROM msdb.dbo.sysmail_sentitems ORDER BY send_request_date DESC

--查看未傳送或正在重試狀態的訊息
SELECT * FROM msdb.dbo.sysmail_unsentitems ORDER BY send_request_date DESC

--查看傳送失敗的訊息
SELECT * FROM msdb.dbo.sysmail_faileditems ORDER BY send_request_date DESC

--查看mail失敗的原因
SELECT SUBSTRING(fail.subject,1,25) AS 'Subject', fail.mailitem_id, LOG.description
FROM msdb.dbo.sysmail_event_log LOG
JOIN msdb.dbo.sysmail_faileditems fail ON fail.mailitem_id = LOG.mailitem_id
WHERE event_type = 'error'

--傳回郵件和狀態佇列
EXEC msdb.dbo.sysmail_help_queue_sp

--顯示Database Mail 佇列的狀態
EXEC msdb.dbo.sysmail_help_status_sp

--可啟動Database Mail 佇列
EXEC dbo.sysmail_start_sp

--可停止Database Mail 佇列
EXEC dbo.sysmail_stop_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'

-- 刪除特定日期之前的紀錄
EXEC sysmail_delete_log_sp @logged_before = '2013-02-28'