SQL Server Database Mail - How to clear the queue


Database Mail is an enterprise solution for sending e-mail messages from the SQL Server Database Engine. Using Database Mail, your database applications can send e-mail messages to users. The messages can contain query results, and can also include files from any resource on your network. Database Mail is designed for reliability, scalability, security, and supportability.

If your Database Mail suddenly stops working then you can try to clear the mail queue and restart the service manually. Unfortunately, there is no function in Management Studio in order to perform this task.

These procedures are presented in a logical order:

1.  Stop SQL Server Agent (otherwise, the queue will be locked)

  • Start SQL Server management Studio
  • Connect to your SQL Server
  • Right-click on SQL Server Agent
  • Choose Stop

2. Click on New Query

3. Empty the mail queue by typing the following statement (then press F5 to execute)

msdb.dbo.sysmail_delete_mailitems_sp

4. Once the queue is empty, stop the service

msdb.dbo.sysmail_stop_sp

5. Now, start the service

msdb.dbo.sysmail_start_sp

6. Restart SQL Server Agent

  • Right-click on SQL Server Agent
  • Choose Start


Commentaires

Fabrice Kauffmann a dit…
Some other interesting commands:

exec msdb..sysmail_help_queue_sp
go

select * from msdb..sysmail_event_log order by log_id desc

select * from msdb..sysmail_allitems
where sent_status <> 'sent'
order by mailitem_id desc
19 janvier 2012 à 13:41
Fabrice Kauffmann a dit…
How to delete events from the Database Mail log (or) How to How to delete all events in the log or those events meeting a date or type criteria ?

exec msdb.dbo.sysmail_delete_log_sp
@logged_before='2010-01-26'

exec msdb.dbo.sysmail_delete_log_sp
@event_type='error'

exec msdb.dbo.sysmail_delete_log_sp
--this deletes entire table
27 mars 2012 à 10:53
Fabrice Kauffmann a dit…
Cleaning the sysmail events,attachments and allitems
in msdb database:

delete from dbo.sysmail_allitems;
delete from dbo.sysmail_attachments;
delete from dbo.sysmail_event_log;
27 mars 2012 à 10:55
Fabrice Kauffmann a dit…
Also a good idea to shrink the MSDB database after a good backup!

DBCC SHRINKFILE(MSDBData, 512)
DBCC SHRINKFILE(MSDBLog, 512)
9 avril 2013 à 15:08
Fabrice Kauffmann a dit…
If a lot of items remain in "unsent" status in msdb..sysmail_allitems, then try to restart SQL Mail by using the two following commands:

sysmail_stop_sp
sysmail_start_sp
16 décembre 2015 à 14:56
Enregistrer un commentaire