I’m an enemy to myself. I’m writing a huge stored procedures and send emails in a loop. And sometimes that loop is infinite.
Anyway, today I had to figure out how to deal with SQL Server mailing functionality and sp_sendmail_dbmail
. Every time you send email via sp_sendmail_dbmail
, your message is going through the a huge number of checks and verifications. (Check out the source of sp_sendmail_dbmail
procedure, it is on msdb
database, inside System Stored Procedures folder.)
After all the checks you eventually will find insert statement: INSERT sysmail_mailitems ...
Now look into sysmail_mailitems
table. You’ll see there a list of all the emails that have been sent, will be sent or failed. There are few views to aid your viewing experience that aggregate data from sysmail_mailitems
into something useful.
Looking at emails is fun Not this bit is a bit of black magic for me, but that is how I understand it. I could be wrong, don’t take it as a reference.
- Whenever
sp_sendmail_dbmail
is executed, info about this email is inserted into sysmail_mailitems
table and into InternalMailQueue
which is not a table, but a queue. (Note for myself, lookup queues in SQL Server).
- That queue is used by a Broker, and whenever there are records in the queue, Broker runs DatabaseMail.exe program.
- The queue holds some information about new mail message and a reference to the record in
sysmail_mailitems
. So DatabaseMail.exe pops a record from the queue, reads respective message from sysmail_mailitems
and sends an email as requested.
-
Now black magic part is almost over. When I have created a 3 million message queue with emails (infinite loop, remember?), all 3 millions records were in sysmail_mailitems
and in InternalMailQueue
. There are helper procedures to remove emails from the pending list:
exec msdb.dbo.sysmail_delete_mailitems_sp null, ‘unsent’
This literally removes all the records from
sysmail_mailitems
with emails that have not been sent yet. (First parameter is date – delete all records older than that date. Second parameter can take ‘sent’, ‘unsent’, ‘failed’, ‘retrying’) But that procedure does not touch anything in InternalMailQueue
. So after executing sysmail_delete_mailitems_sp I was left with no records in the table, but all of them were in the queue. Now, when DatabaseMail.exe runs, it will find a lot of messages in the Queue, but no records about the mail to be sent. So it will create a lot of “FAIL!” records in sysmail_log
table. To clean up InternalMailQueue
at the moment I’m running this query:
#!sql
exec msdb.dbo.sysmail\_stop\_sp -- stop DatabaseMail.exe, so it does not try hard
ALTER QUEUE ExternalMailQueue WITH STATUS = ON
set nocount on
declare @Conversation_handle uniqueidentifier;
declare @message_type nvarchar(256);
declare @counter bigint;
declare @counter2 bigint;
set @counter = (select count(*) from ExternalMailQueue) -- count millions of queued messages
set @counter2=0
-- now do until nothing is left in the queue
while (@counter2<=@counter)
begin
receive @Conversation_handle = conversation_handle, @message_type = message_type_name from ExternalMailQueue -- pop messages from the queue
set @counter2 = @counter2 + 1
end
exec msdb.dbo.sysmail_start_sp -- start DatabaseMail.exe again
EXEC msdb.dbo.sysmail_help_queue_sp -- verify that there is nothing left in the queue
Hopefully I won’t need this article again, but can be useful for somebody facing the same problem. Most of the details were spread around the internet, but I could not find the big picture.
Reference:
- Using and Managing Databse Mail
- Forum discussion about message clearing
- MSDN: Troubleshooting Database Mail: General steps
- MSDN: Database Mail Architecture
- Database Mail Setup – SQL Server 2005
- Found this after I have wrote this: SQL Server 2008 : Sending and Receiving with Database Mail