SQL Server 2008 資料庫郵件完整設定指南：SMTP 設定、sp_configure 啟用、測試寄信與日誌查詢

前言：Database Mail 架構優勢

SQL Server 2005 引入 Database Mail 取代舊版 SQL Mail：

• 基於標準 SMTP，無需 MAPI/Outlook。
• 非同步佇列處理，不阻塞引擎執行緒。
• 支援附件、查詢結果附件、HTML 郵件、多設定檔/帳戶。
• 內建重試、日誌、狀態追蹤。

一、SSMS 圖形化設定精靈步驟

1. SSMS → 管理 → 資料庫郵件 → 右鍵 設定資料庫郵件。
2. 選擇 設定資料庫郵件：啟用資料庫郵件、建立設定檔與帳戶。
3. 新增設定檔：名稱 DBA_Alert_Profile、描述。
4. 新增 SMTP 帳戶：
   ▸ 帳戶名：Gmail_SMTP
   ▸ 電子郵件：alert@company.com
   ▸ 顯示名稱：SQL Server Alert
   ▸ 回覆郵件：dba@company.com
   ▸ SMTP 伺服器：smtp.gmail.com (或內部 Exchange)
   ▸ 連接埠：587 (TLS) 或 465 (SSL) 或 25
   ▸ 驗證：基本驗證，輸入帳號/密碼 (或 OAuth2 需外掛)。
   ▸ 啟用 SSL (Gmail/Office365 必須)。
5. 設定檔安全性：公開設定檔 (所有 msdb 使用者可用) 或 私人設定檔 (指定使用者/角色)。
6. 系統參數：保留預設 (重試 5 次、間隔 60 秒、最大附件 1MB 等) 或依需求調整。
7. 完成精靈。

二、啟用 Service Broker 與 Database Mail XPs (必要步驟)

-- 1. 確認 msdb 啟用 Service Broker (Database Mail 依賴佇列)
SELECT is_broker_enabled FROM sys.databases WHERE name = 'msdb';
-- 若為 0，需獨佔模式啟用：
-- ALTER DATABASE msdb SET ENABLE_BROKER WITH ROLLBACK IMMEDIATE;

-- 2. 啟用 Database Mail XPs (預設關閉)
EXEC sp_configure 'show advanced options', 1; RECONFIGURE;
EXEC sp_configure 'Database Mail XPs', 1; RECONFIGURE;
-- 立即生效，無需重啟服務

三、發送測試郵件

USE msdb;
GO
EXEC dbo.sp_send_dbmail
    @profile_name = 'DBA_Alert_Profile',  -- 精靈建立的設定檔名
    @recipients   = 'dba@company.com',    -- 多位址用 ; 分隔
    @subject      = 'SQL Server Database Mail 測試',
    @body         = '這是來自 ' + @@SERVERNAME + ' 的測試郵件，時間：' + CONVERT(VARCHAR, GETDATE(), 120),
    @body_format  = 'TEXT';               -- 或 'HTML'
GO

四、進階寄信範例

1. 附加查詢結果 (CSV/Tab)

EXEC dbo.sp_send_dbmail
    @profile_name = 'DBA_Alert_Profile',
    @recipients   = 'dba@company.com',
    @subject      = '每日備份狀態報表',
    @query        = 'SELECT database_name, backup_start_date, backup_finish_date, backup_size/1024/1024 AS Size_MB FROM msdb..backupset WHERE backup_start_date >= DATEADD(DAY,-1,GETDATE()) ORDER BY backup_start_date DESC',
    @attach_query_result_as_file = 1,
    @query_attachment_filename = 'BackupReport.csv',
    @query_result_separator = CHAR(9),  -- Tab
    @query_result_header = 1;

2. 附加檔案 (需檔案在伺服器可存取路徑)

EXEC dbo.sp_send_dbmail
    @profile_name = 'DBA_Alert_Profile',
    @recipients   = 'dba@company.com',
    @subject      = '錯誤日誌備份',
    @body         = '請參閱附件錯誤日誌。',
    @file_attachments = 'C:\Logs\ERRORLOG.1;C:\Logs\ERRORLOG.2';

3. HTML 格式郵件

DECLARE @html NVARCHAR(MAX) = N'
備份失敗警報
';
SELECT @html += ''
FROM msdb..backupset WHERE backup_finish_date >= DATEADD(HOUR,-1,GETDATE()) AND backup_size = 0; -- 示意
SET @html += '
資料庫
時間
錯誤
' + database_name + '
' + CONVERT(VARCHAR, backup_finish_date, 120) + '
' + message + '
';
EXEC dbo.sp_send_dbmail @profile_name='DBA_Alert_Profile', @recipients='dba@company.com', @subject='備份失敗', @body=@html, @body_format='HTML';

五、查詢郵件日誌與狀態

-- 1. 郵件事件日誌 (發送/失敗/重試)
SELECT * FROM msdb.dbo.sysmail_log ORDER BY log_date DESC;

-- 2. 郵件項目狀態 (sent/unsent/retrying/failed)
SELECT
    mailitem_id, profile_id, recipients, subject,
    sent_status, sent_date, last_mod_date, retry_count
FROM msdb.dbo.sysmail_mailitems
ORDER BY sent_date DESC;

-- 3. 失敗郵件詳細錯誤
SELECT
    mi.mailitem_id, mi.recipients, mi.subject,
    l.description AS error_description, l.log_date
FROM msdb.dbo.sysmail_mailitems mi
JOIN msdb.dbo.sysmail_log l ON mi.mailitem_id = l.mailitem_id
WHERE mi.sent_status = 3  -- 3 = failed
ORDER BY l.log_date DESC;

六、常見問題排查

結語

Database Mail 是 SQL Server 內建、企業級、無相依的郵件發送方案。核心設定三步驟：SMTP 帳戶/設定檔 → 啟用 Service Broker + Database Mail XPs → sp_send_dbmail 發送。善用查詢結果附件、HTML 格式、日誌監控，可建立完整的自動化警報、報表分發、審計通知體系。後續可整合 Agent 作業、WMI 警示、AlwaysOn 事件通知，實現全方位資料庫營運感知。