We are facing the issue with DB mails in sql server 2014.
Suddenly, it stops sending emails to users stating UNSENT status in msdb.dbo.sysmail_allitems.
Please help.
Check out if someone disable broker or disable sending e-mail. These two queries should return 1.
SELECT value_in_use
FROM sys.configurations
WHERE name = 'Database Mail XPs';
SELECT is_broker_enabled
FROM sys.databases
WHERE name='MSDB';
Is there records in the transmission queue? No record means good. If there is a record, pay attention on field transmission_status. This is written the reason this message is on the queue. This is generally an error message explaining why sending the message failed.
select * from msdb.sys.transmission_queue ;
Try cleaning 'ExternalMailQueue'
WHILE EXISTS
(
SELECT 'ExternalMailQueue' AS ServiceBrokerQueueName,
*
FROM [msdb].[dbo].[ExternalMailQueue]
)
BEGIN
WAITFOR(
RECEIVE TOP (1) conversation_group_id FROM [ExternalMailQueue]), TIMEOUT 1000;
END;
msdb.dbo.sysmail_event_log. What kind of errors are you getting in thedescriptioncolumn?