October 21, 2016 at 7:20 am
Is it possible to make contingency for when SQL sends a mail as a result of an alert but the mail server simply fails to deliver? I think I know the answer her but checking.
Found nothing on google but advise on how to set up Dbmail; it has worked perfectly until this one incident.
...
October 24, 2016 at 5:30 am
The query below shows information about how mail has been processed according to SQL Server (see [sent_status] column). But if the mail failed on the SMTP-server without a proper feedback to SQL, SQL will be unaware of that failure.
-- show a list of all mail-items
SELECT
sysmail_allitems.mailitem_id
, sent_status
, recipients
, subject, body
, send_request_date
, send_request_user
, sent_date
, sysmail_allitems.last_mod_date
, sysmail_event_log.event_type
, sysmail_event_log.description
FROM msdb.dbo.sysmail_allitems
LEFT OUTER JOIN msdb.dbo.sysmail_event_log
ON sysmail_allitems.mailitem_id = sysmail_event_log.mailitem_id
where send_request_date > dateadd(dd, -7, getdate())
--and sent_status = 'failed'
order by
send_request_date desc
October 24, 2016 at 5:37 am
HappyGeek (10/21/2016)
Is it possible to make contingency for when SQL sends a mail as a result of an alert but the mail server simply fails to deliver? I think I know the answer her but checking.Found nothing on google but advise on how to set up Dbmail; it has worked perfectly until this one incident.
Perhaps you can additionally query (all) the SQL servers from a central management location for raised alerts and check the results against the mails sent/received? But you have to wonder if it is important enough to put that much effort in something that normally works for 99% of the time.
October 28, 2016 at 11:50 pm
Think we have convinced management there are some things that it is not possible to mitigate for! Used a similar query to show management the agent had done what it was supposed to, thanks.
...
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply