April 2, 2018 at 5:52 pm
Hello All,
I'm unable to get Database Mail to send email from SQL 2016 EE server. When I review test emails sent all emails show "unsent" status and I cannot get it to work.
I've tried the following below:
The one interesting item is the following timestamp below is showing several hours ahead (i.e. today 11:12 PM)
queue_type length state last_empty_rowset_time last_activated_time
mail 24 INACTIVE 2018-04-02 23:12:45.373 2018-04-02 23:12:45.363
When I check SQL Server it is returning the current date/time (i.e. 2018-04-02 18:48:49.070). Why would it show time in the future?
Thanks for any input or guidance on this issue.
-Dave
April 3, 2018 at 4:40 am
You can check the below link which might be helpful for you in troubleshooting database mail issues.
Regarding the future date it shows - Microsoft Documentation says "military time format and GMT Time Zone"
Hope this helps..
-Hari Mindi
April 3, 2018 at 5:37 pm
davidsalazar01 - Monday, April 2, 2018 5:52 PMHello All,I'm unable to get Database Mail to send email from SQL 2016 EE server. When I review test emails sent all emails show "unsent" status and I cannot get it to work.
- enabled Database_Mail XP's (i.e sp_configure 'Database Mail XPs', 1) go reconfigure with override go
- setup default Datamail Mail profile that is used on other SQL Servers with no issues. Valid email address, smtp server name, etc.
- EXEC msdb.dbo.sysmail_stop_sp;
- EXEC msdb.dbo.sysmail_start_sp;
- EXEC msdb.dbo.sysmail_delete_mailitems_sp @sent_before = @getdate-2;
- .NET 3.5 is already installed on the server from the SQL 2016 installation sysprep already
- repair SQL Server installation (included Database Mail)
- restarted SQL services (engine/agent) multiple times and rebooted Windows 2012 server 2x (once before repair after)
- Enabled SQL profile in SQL Agent/Disabled and re-enabled. Restarted SQL Agent thereafter.
- NEXT step...kick the server
The one interesting item is the following timestamp below is showing several hours ahead (i.e. today 11:12 PM)
queue_type length state last_empty_rowset_time last_activated_time
mail 24 INACTIVE 2018-04-02 23:12:45.373 2018-04-02 23:12:45.363When I check SQL Server it is returning the current date/time (i.e. 2018-04-02 18:48:49.070). Why would it show time in the future?
Thanks for any input or guidance on this issue.
-Dave
sysmail_help_queue_sp only has the status as receives occurring when the mail process is active. It shuts itself down after 10 minutes of inactivity.
Are you checking this immediately after sending a test email?
Have you tried querying msdb.dbo.sysmail_event_log to look for any errors and the descriptions?
If you are using a corporate mail server, has the server been allowed to access for SMTP relay?
Sue
April 4, 2018 at 4:09 pm
Hi Sue,
Yes, I've checked the status immediately after I've sent a test email using EXEC msdb.dbo.sysmail_help_queue_sp @queue_type = 'mail';
I'm not sure if the server is allowed for SMTP relay access. Are you asking if SMTP service is running on the server? We typically execute a powershell script when we setup new SQL Servers to config new items and part of that script enables Database Mail and setups the mail profile for our SQL team. We never had any issues until now and the script wasn't changed. I see the profile is there as usual, but can't send email when testing. The only difference is an App Team enabled DB Mail and created a profile for their team, before I deployed our powershell script.
Thanks,
David
April 4, 2018 at 4:48 pm
davidsalazar01 - Wednesday, April 4, 2018 4:09 PMHi Sue,Yes, I've checked the status immediately after I've sent a test email using EXEC msdb.dbo.sysmail_help_queue_sp @queue_type = 'mail';
I'm not sure if the server is allowed for SMTP relay access. Are you asking if SMTP service is running on the server? We typically execute a powershell script when we setup new SQL Servers to config new items and part of that script enables Database Mail and setups the mail profile for our SQL team. We never had any issues until now and the script wasn't changed. I see the profile is there as usual, but can't send email when testing. The only difference is an App Team enabled DB Mail and created a profile for their team, before I deployed our powershell script.Thanks,
David
Sue I tried to test smtp relays using the template below subbing our info and it doesn't process. Is this what you meant in your explanation earlier?
telnet smtp.OurDomainName.tdl
rset
ehlo
rset
mail from:FromEmail@OurDomainName.com
rcpt to:ToEmail@OurDomainName.com
April 4, 2018 at 4:53 pm
davidsalazar01 - Wednesday, April 4, 2018 4:09 PMHi Sue,Yes, I've checked the status immediately after I've sent a test email using EXEC msdb.dbo.sysmail_help_queue_sp @queue_type = 'mail';
I'm not sure if the server is allowed for SMTP relay access. Are you asking if SMTP service is running on the server? We typically execute a powershell script when we setup new SQL Servers to config new items and part of that script enables Database Mail and setups the mail profile for our SQL team. We never had any issues until now and the script wasn't changed. I see the profile is there as usual, but can't send email when testing. The only difference is an App Team enabled DB Mail and created a profile for their team, before I deployed our powershell script.Thanks,
David
Not asking if SMTP is running on the server but if it's allowed for SMTP relay access. Ask whoever manages your email server.
To make sure you can get to that server, you should be able to telnet to the port. You can get the server name and port by querying sysmail_server table in msdb. From the command prompt execute:
telnet ServerName PortNumber
You would want to still query sysmail_event_log in msdb - especially look at the event type and description columns: SELECT *
FROM sysmail_event_log
ORDER BY log_date desc
I wouldn't assume that it's configured correctly as scripts can get changed without anyone knowing and whoever created another mail profile could have changed whatever was already set up. All could be unintentional but it's sometimes best not to assume all configurations are correct. Things happen.
Sue
April 11, 2018 at 3:07 am
Hi David
Which version of SQL Server are you running and did you apply any CU lately?
Kal
April 11, 2018 at 11:22 am
Hi Kal,
SQL Server version is 2016 RTM. I'm working on applying SP1 on the server, but awaiting app team approval as they are testing. Is there a known issue with DB Mail on SQL 2016 RTM?
Thanks,
David
April 11, 2018 at 11:47 am
Hi David
I ran into an issue when applying SP1 today it seems one column of the sysmail_server table. One column called timeout gets removed but it gets reinserted if you apply the CU. I applied CU8 and the database mail didn’t run so I removed it and applied CU7 and that worked.
So to confirm my advice:
Check the table structure now
Apply SP1 and check the table structure
Apply CU7 and check database mail
Good luck
Kal
April 11, 2018 at 2:43 pm
Hi Kal,
I applied SP1 and it worked. I'm going to assume (even though I shouldn't) that CU7/CU8 is part of SP1? Or are they post-SP1?
Post reply
Thanks all for you help!
David
April 11, 2018 at 10:17 pm
Hi David
Great News!!
It didnt work for me until i applied CU7
CU7 gets applied atop of SP1 to deal with bugs and / or software issues which were discovered after SP1 was released
Have a good day
Kal
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply