0

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.

1
  • Have a look at the logs for the failed items in msdb.dbo.sysmail_event_log. What kind of errors are you getting in the description column? Commented Apr 29, 2019 at 13:12

1 Answer 1

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;
Sign up to request clarification or add additional context in comments.

Comments

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.