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_dbmailis executed, info about this email is inserted intosysmail_mailitemstable and intoInternalMailQueuewhich 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 fromsysmail_mailitemsand 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_mailitemsand inInternalMailQueue. 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: