Database Mail 因大量發信作業，導致SQL Database Mail功能失效問題解決

2014-09-15

3033
0
2014-09-16

緣由 :

之前遇見一台SQL的Database Mail功能整個喪失，所有的信件都QUEUE住，無法順利送出。

經查證後發現某一段時間同仁測試Store Procedure時因為Bug導致大量發送信件，結果把Database

Mail的功能給搞死了。

當然只要從啟SQL服務則Database Mail功能就會回復，但在一台Online的SQL上，相信不可能讓

我們動不動就去重啟它，因此必須找出讓SQL不用重啟就可以恢復的方式。我在寫這一篇文章時，

也尚未發現正確的SOP，在實驗時有時按照某個順序流程可以，但再試一次又不行，所以我真的不

知到解決的SOP。

產生大量MAIL的TSQL

declare @i int ;

set @i=0;

while @i<500

begin

declare @msg varchar(512) ;

select @msg='Test' ;

EXEC msdb.dbo.sp_send_dbmail

@profile_name = 'SendMail',

@recipients = 'XXX@mail.rock.com.tw',

@subject = 'SQLServer Test',

@body = @msg,

@body_format=TEXT;

set @i+=1;

end

感謝 Colin 老師看見此篇文章後，分享他的解決方法。

經過小弟LAB驗證是可行的，而且流程更加簡單。

/*關閉Database Mail功能*/
sp_configure 'Database Mail XPs', 0
RECONFIGURE WITH OVERRIDE
GO

/*刪除大量Queue的信件*/
DECLARE @GETDATE datetime
SET @GETDATE = GETDATE();
EXECUTE msdb.dbo.sysmail_delete_mailitems_sp @sent_before = @GETDATE;
GO

/*開啟Database Mail功能*/
sp_configure 'Database Mail XPs', 1
RECONFIGURE WITH OVERRIDE
GO

<<以下是小弟之前的作法，但成功率不是很高>>

可能的解決問題之SOP

1 . 執行sysmail_stop_sp來關閉Database Mail功能，如有被Block住，Kill相關Session。

2 . 到OS中開啟工作管理員，去找出DatabaseMail.exe這一個處理程序，並結束該程序。

3 . 利用sysmail_delete_mailitems_sp來移除所有Queue住的Mail。

4 . 檢視msdb中[msdb].[dbo].[sysmail_allitems]這一個View，看看Queue住的Mail是否都清空。

也可以執行msdb.dbo.sysmail_help_queue_sp ，系統會回傳Queue的狀態。

5 . 執行sysmail_start_sp來啟用Database Mail功能。

6 . sp_who2來看看是否有Session為Database Mail。

有朋友表示他也會執行下面的語法，他表示SQL也是用CLR的方式來發信。但我發現我的DB即使沒啟用

CLR還是可以正常發信，所以大家參考嘍。

EXEC sp_configure 'clr enabled', 0 (關閉CLR，Database Mail也是利用CLR方式)。