April 11, 2017 at 11:57 am
We experienced an oddity, starting out of the blue a couple of weeks ago. I think that there's an issue outside of SQL Server, but I am trying to gather all the data I can before approach our network team.
We send about 400 emails each morning through sp_send_dbmail, which at some point was throttled back to send an email every 4 seconds. (That 4-second wait was instituted because otherwise the emails will start to fail after 50-75 with the same error that I'm starting to see now):
The mail could not be sent to the recipients because of the mail server failure. (Sending Mail using Account 2 (2017-04-03T15:31:30).
Exception Message: Cannot send mails to mail server. (The operation has timed out.).
The email gets re-queued for an additional single attempt, waiting 60 seconds before the retry (these are the Instance level parms for emails). What I'm finding is that an email isn't noted as a failure in sysmail_faileditems unless the re-try is noted as a failure. In any case, whether or not the first/second email fails from SQL Server's perspective, these emails are actually being sent to the recipients. So, we're seeing duplicate emails go out. So, despite the timeout message, the email is always going through to Exchange.
The day prior to this issue arising, the difference between sysmail_allitems.send_request_date, and sysmail_allitems.sent_date was 1-2 seconds. Starting on the day of the problem, I'm seeing at least 20 seconds between these two columns for the emails delivered first in the queue, and then as much as 8 minutes for the emails which are noted as failing to send on both attempts.
I'm not finding anything in the Event Viewer noting this failure, and I'm not sure if there's anything else I can bring to my Network team. Can you think of anything? I've tried increasing the timing between emails to 10 seconds, as well as sending fewer emails, but we're still seeing the issue.
Thanks,
--=Chuck
April 11, 2017 at 1:45 pm
If it helps, I can get the same behavior (duplicate emails starting at about the 40th email) by running something like the following, without any delay between emails. It happens in SQL 2005, up to SQL 2014:
begin
declare @i int = 1,
@l_subject varchar(20);
while @i <= 100
begin
set @l_subject = 'Test Email '+convert(varchar(3), @i);
exec msdb.dbo.sp_send_dbmail
@profile_name = '<your profile>',
@recipients = '<your email address>',
@body = 'Test',
@subject = @l_subject;
set @i = @I + 1;
end;
end;
end;
April 11, 2017 at 1:49 pm
chuck.forbes - Tuesday, April 11, 2017 1:45 PMIf it helps, I can get the same behavior (duplicate emails starting at about the 40th email) by running something like the following, without any delay between emails. It happens in SQL 2005, up to SQL 2014:
begin
declare @i int = 1,
@l_subject varchar(20);
while @i <= 100
begin
set @l_subject = 'Test Email '+convert(varchar(3), @i);
exec msdb.dbo.sp_send_dbmail
@profile_name = '<your profile>',
@recipients = '<your email address>',
@body = 'Test',
@subject = @l_subject;
set @i = @I + 1;
end;end;
end;
Sorry, SQL 2005 -> 2014 compliant code would be the following:
begin
declare @i int,
@l_subject varchar(20);
set @i = 1;
while @i <= 100
begin
set @l_subject = 'Test Email '+convert(varchar(3), @i);
exec msdb.dbo.sp_send_dbmail
@profile_name = '<your profile>',
@recipients = '<your email address>',
@body = 'Test',
@subject = @l_subject;
set @i = @i + 1;
end;
end;
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply