Unable to clear SQL Server sysmail queue

Question

Last night due to some bad code, my SQL Server tried sending a million emails. After 10k the emails started going into queue. I immediately stopped the sysmail service using exec sysmail_stop_sp.

Then I deleted all entries from sysmail_allitems, sysmail_faileditems, sysmail_send_retries, sysmail_senditems and sysmail_unsentitems.

I also cleared sysmail_event_log.

Assuming that it would clear my mail queue I started the sysmail services using

exec sysmail_start_sp

But to my surprise the sysmail is still trying to send emails but it cannot find them in sysmail_allitems.

I checked the mail queue using msdb.dbo.sysmail_help_queue_sp and it shows queue with queue_type 'mail' showing those million emails queue.

I rechecked all the tables and they all are empty. Where is this queue is being fetched?

Though no email were sent but still the server tried each and every email which lasted for few hours.

Any idea where the queue is fetched from?

Answer 1

1. Stop SQL Server Agent
2. Set it to start manually (using SQL Server Configuration Manager)
3. Restart SQL Server when you can.
4. Try to execute:

DECLARE @GETDATE datetime  
SET @GETDATE = GETDATE();  
EXECUTE msdb.dbo.sysmail_delete_mailitems_sp @sent_before = @GETDATE;  
GO 

This should remove them all. The reason I mentioned stopping agent is so that it won't take a lock on them and stop you deleting them.

5. Set Agent back to starting auto.
6. Restart Agent

And hopefully there won't be a stream of emails coming out. You'll also have to check for any that are queued up in the mail server as well.

Hope that helps.