Database Mail - Not sending mail

Question

Database Mail - Not sending mail

cs_source

Default port

Points: 1459
More actions
June 13, 2017 at 12:05 pm

#343241

Hi there,
I have been attempting to troubleshoot my issue with email not being sent from the server and i'm running out of ideas. I have searched around completed the following:
First thing i have removed all mailitems:
DECLARE @getdate-2 datetime
SET @getdate-2 = GETDATE();
EXECUTE msdb.dbo.sysmail_delete_mailitems_sp @sent_before = @getdate-2;
GO
I ensure that Database mail is configured using this command:
sp_configure 'show advanced options', 1;
GO
RECONFIGURE;
GO
sp_configure 'Database Mail XPs', 1;
GO
RECONFIGURE
GO
I have stopped and started the service:
EXEC msdb.dbo.sysmail_stop_sp;
EXEC msdb.dbo.sysmail_start_sp;

I confirmed the status of system has started:
EXECUTE dbo.sysmail_help_status_sp
I have SQL2008 which sends mail great and i mirrored all the settings for the database mail configuration and still no luck.
When i look here: SELECT * FROM sysmail_allitems
I see a sent status of unsent.

Any advice would be appreciated!

Viewing 5 posts - 1 through 4 (of 4 total)

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

Lowell SSC Guru Points: 323518 More actions · Answer 1

there's an additional table to join to, the msdb.dbo.sysmail_event_log , where the [description] column will tell you exactly what the issue is;
it's typically an error from the mail server, username password required, relaying not allowed, etc
if you post the exact error message, we can help a bit better
SELECT top 100 mail.send_request_date As SentDate, sent_status As Reason, err.[description], mail.* FROM [msdb].[dbo].[sysmail_allitems] mail inner join [msdb].[dbo].[sysmail_event_log] err ON err.mailitem_id = mail.mailitem_id WHERE mail.sent_status <> 'sent' order by mailitem_id desc

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

cs_source Default port Points: 1459 More actions · Answer 2

Hi Lowell,

Thank you for the insight, I actually found the resolution which was actually on a reddit thread.

https://www.reddit.com/r/SQLServer/comments/4rd5kg/sql_server_2016_database_mail_and_net_35_framework/

You don't need to install .NET 3.5. You only need to edit a config file.

https://connect.microsoft.com/SQLServer/feedback/details/2780580/sql-2016-rtm-databasemail-not-working

Another less invasive workaround you may want to try is to put the missing config file next to the DatabaseMail.exe (see attachment).
The file needs to be put under "C:\Program Files\Microsoft SQL Server\MSSQL13.MSSQLSERVER\MSSQL\Binn" (actual path depends on the drive where you installed SQL2016 and the name of your SQL instance).
If the attachment file did not go thru, here's the content of that file (DatabaseMail.exe.config):

<?xml version="1.0"?><configuration><startup useLegacyV2RuntimeActivationPolicy="true">  <supportedRuntime version="v4.0"/>  <supportedRuntime version="v2.0.50727"/></startup></configuration>

goutams_mail SSC Rookie Points: 36 More actions · Answer 3

This post help me to resolve the same issue.. I was struggling because I didn't see any configuration issues in mail profile/account or anywhere.. Finally I created the DatabaseMail.exe.config file as mentioned in this post and it started working.

Singhka Newbie Points: 1 More actions · Answer 4

Singhka

Newbie

Points: 1

November 19, 2020 at 2:04 pm

#3811596

This post help me to resolve the same issue