3

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?

Dale K
  • 25,246
  • 15
  • 42
  • 71
Shan Nautiyal
  • 129
  • 5

1 Answers1

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.

  1. Set Agent back to starting auto.
  2. 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.

Greg Low
  • 1,526
  • 1
  • 4
  • 4
  • Why stop SQL Agent? SQL Server Database Mail is independent of SQL Agent and uses a service broker implementation to launch the external DatabaseMail.exe delivery program via msdb.dbo.sp_sysmail_activate. If anything you want to stop the Database Mail Service via sysmail_stop_sp, delete the queue, then restart mail with sysmail_start_sp. – AlwaysLearning Sep 23 '19 at 06:21
  • My question is to know from where does 'msdb.dbo.sysmail_help_queue_sp' fetches the queue length. After deleting all mail items, this procedure was still returning queue length in millions. – Shan Nautiyal Sep 23 '19 at 11:12