November 23, 2016 at 11:46 am
I have three SQL Agent jobs on SQL Server 2012 RTM that failed to notify me. I can send a test email to myself so that works. I created a job that I knew would fail due to a syntax error and then I ran the below:
EXEC msdb.dbo.sysmail_help_status_sp;
EXEC msdb.dbo.sysmail_help_queue_sp @queue_type = 'mail';
SELECT * FROM msdb.dbo.sysmail_event_log WHERE log_date > '2016-11-23';
I got back the following.
STARTED
length = 0, state = RECEIVES_OCCURRING, last_empty_rowset_time = 2016-11-23-1836:13.873, last_activated_time = 2016-11-23-1836:12.623
log_date = 2016-11-23 12:36:12.800, description = DatabaseMail process is started
I have also checked that the SQL Server Agent is set up to use database mail by right clicking "SQL Server Agent" and selecting Properties and Alert System page.
Is there something else I should be checking before asking other IT folks to look at the email system, etc.?
November 23, 2016 at 12:19 pm
you can check the sent_status of all queued e-mails:
SELECT * FROM dbo.sysmail_allitems
WHERE send_request_date >= DATEADD(day, DATEDIFF(day, 0, GETDATE()), 0)
ORDER BY send_request_date DESC
there's also an error log:
SELECT i.mailitem_id, i.subject, i.send_request_date, l.log_date, i.body, i.file_attachments, l.description
FROM dbo.sysmail_faileditems as i
INNER JOIN dbo.sysmail_event_log AS l ON i.mailitem_id = l.mailitem_id
WHERE i.last_mod_date >= DATEADD(day, DATEDIFF(day, 0, GETDATE()), 0)
ORDER BY i.send_request_date DESC, l.log_date DESC
November 23, 2016 at 12:34 pm
Thanks Chris.
The query against sysmail_allitems returns the test email I sent myself and it shows the two attempts to generate an email from a failed SQL Agent job I was using to test database mail with. I received the test email from the DBMail wizard but not the test emails I tried to generate from the test job.
I get an empty result set from the sysmail_faileditems query.
November 23, 2016 at 12:50 pm
in the first query results, what was sent_status for the SQL Agent jobs?
November 23, 2016 at 1:02 pm
It shows "Sent."
November 23, 2016 at 1:18 pm
Then it's time to take a look outside SQL Server, see if it got stuck in the Exchange server somewhere or in a spam filter.
November 23, 2016 at 7:56 pm
You'll also want to white-list the sending address to ensure they make it through.
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply