Db Mail not seemingly working

Question

Db Mail not seemingly working

Adam Sandler

SSCrazy

Points: 2697
More actions
November 18, 2015 at 5:41 am

#305163

I have check all below - all seems ok, there is nothing in the error file , just that dbmail gets started.
https://technet.microsoft.com/en-us/library/ms187540(v=sql.105).aspx
Where should I now be looking for the problem. This was working fine till a month ago, and then stopped, just getting round to finding out why.
Thanks

Viewing 15 posts - 1 through 14 (of 14 total)

You must be logged in to reply to this topic. Login to reply

Ant-Green SSC Guru Points: 113507 More actions · Answer 1

Check any firewalls (software / hardware) for any outbound rules from your SQL server to your SMTP server for port 25 access (assuming you are using port 25)

Telnet from your SQL server to the SMTP server over port 25, do you get the SMTP server response? If so type HELO and you should get a response back.

Speak with your mail admins in case they have made any changes to the SMTP server. If using Exchange / CAS you need to specify that the IP addresses of the servers can connect and relay mail, the rule may have been for a range of IP's which has been locked down now to specific IPs

Does your SMTP server now require authentication? Has the authentication mode changed? User account password expired?

Any errors in msdb.dbo.sysmail_event_log

mark.williams 37494 SSCommitted Points: 1661 More actions · Answer 2

anthony.green (11/18/2015)
Check any firewalls (software / hardware) for any outbound rules from your SQL server to your SMTP server for port 25 access (assuming you are using port 25)
Telnet from your SQL server to the SMTP server over port 25, do you get the SMTP server response? If so type HELO and you should get a response back.
Speak with your mail admins in case they have made any changes to the SMTP server. If using Exchange / CAS you need to specify that the IP addresses of the servers can connect and relay mail, the rule may have been for a range of IP's which has been locked down now to specific IPs
Does your SMTP server now require authentication? Has the authentication mode changed? User account password expired?
Any errors in msdb.dbo.sysmail_event_log

Spot on

_________________________________________________________________

"The problem with internet quotes is that you cant always depend on their accuracy" -Abraham Lincoln, 1864

Adam Sandler SSCrazy Points: 2697 More actions · Answer 3

Thanks - I'll investigate as advised and post back.

Adam Sandler SSCrazy Points: 2697 More actions · Answer 4

Well the answer was

SMTP relay was changed , so I just changed the server in the account configuration.

Am I missing something - why didn't that error show up , rather than me having to ask the mail admin if anything was changed?

Thanks

Ant-Green SSC Guru Points: 113507 More actions · Answer 5

Was there anything in the sysmail_event_log table detailing the failures?

Adam Sandler SSCrazy Points: 2697 More actions · Answer 6

No the only thing ever in that table is process starting and the process shutting down?

mark.williams 37494 SSCommitted Points: 1661 More actions · Answer 7

See if this returns anything, changing recipient

USE msdb ;

GO

-- Show the subject, the time that the mail item row was last

-- modified, and the log information.

-- Join sysmail_faileditems to sysmail_event_log

-- on the mailitem_id column.

-- In the WHERE clause list items where danw was in the recipients,

-- copy_recipients, or blind_copy_recipients.

-- These are the items that would have been sent

-- to danw.

SELECT items.subject,

items.last_mod_date

,l.description FROM dbo.sysmail_faileditems as items

INNER JOIN dbo.sysmail_event_log AS l

ON items.mailitem_id = l.mailitem_id

WHERE items.recipients LIKE '%name of recipient%'

OR items.copy_recipients LIKE '%name of recipient%'

OR items.blind_copy_recipients LIKE '%name of recipient%'

GO

_________________________________________________________________

"The problem with internet quotes is that you cant always depend on their accuracy" -Abraham Lincoln, 1864

Adam Sandler SSCrazy Points: 2697 More actions · Answer 8

No it show no records, or non from the last few months anyway, none to my test email attempts (tried it locally on another test instance, shows records)?

Is it somehow configured not to record errors?

Ant-Green SSC Guru Points: 113507 More actions · Answer 9

Just check sysmail_faileditems on its own or sysmail_allitems to see if any recorded any errors.

Adam Sandler SSCrazy Points: 2697 More actions · Answer 10

All my test email, which where never received are in sysmail_allitems marked as sent_status sent._

Ant-Green SSC Guru Points: 113507 More actions · Answer 11

SQL sent the mail successfully to your SMTP server.

Speak with your mail admins again to see if the old SMTP server is still alive. It may just have its sending disabled meaning mail is sat in the queue ready to be sent, but isn't going anywhere as it can't send it on.

As far as SQL is concerned, once its passed the payload to the SMTP server it accepts that as a sent mail, its then SMTP's responsibility to do the passing on to the user.