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.
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:
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
-- now do until nothing is left in the queue
receive @Conversation_handle = conversation_handle, @message_type = message_type_name from ExternalMailQueue -- pop messages from the queue
set @counter2 = @counter2 + 1
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.
- 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