SQL server stops sending db mails in sql server 2014

Question

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.

Answer 1

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;