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:

1. Using and Managing Databse Mail
2. Forum discussion about message clearing
3. MSDN: Troubleshooting Database Mail: General steps
4. MSDN: Database Mail Architecture
5. Database Mail Setup – SQL Server 2005
6. Found this after I have wrote this: SQL Server 2008 : Sending and Receiving with Database Mail