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
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
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
in msdb database:
delete from dbo.sysmail_allitems;
delete from dbo.sysmail_attachments;
delete from dbo.sysmail_event_log;
DBCC SHRINKFILE(MSDBData, 512)
DBCC SHRINKFILE(MSDBLog, 512)
sysmail_stop_sp
sysmail_start_sp