July 29, 2016 at 1:40 pm
Background:
There is a SQL Agent job that's been running for years (original developer is gone), which sends about 300+ emails a day, and there's a 4-second delay between sends. I've been asked to figure out if I can remove the timer, since this program takes at least 20 minutes a day, and there are items after this which can't run until all of the emails go out.
It looks like if I remove the timer, or decrease it anywhere beneath 4 seconds, that we'll send duplicate emails (the quicker I send them, the more duplicates I see). I was reading that when SQL Server thinks that the SMTP server is unavailable, the email is marked as failed, and if Account Retry Attempts is > 0 then the email will be re-tried (after the number of seconds depicted in Account Retry Delay). It looks like that's certainly what's happening here, because I see the duplicate emails marked as failed in the sysmail_faileditems table/view.
Following a suggestion I found online, if I set Account Retry Attempts to 0, I send the exact number of emails I'm expecting. In this case I still see failed emails sitting in sysmail_faileditems (despite their successful delivery).
So, in actuality these aren't failing. And I'd prefer that emails be re-tried in the case that the SMTP server isn't available. For now I'm going to follow my cautious side and leave the Account Retry Attempts at a value >0 and leave the 4-second timer in there. But is there something else that I can tweak (or ask our Exchange admin to tweak) so that emails that are actually sent aren't marked as failed, or that the SMTP server can process requests faster?
I'm just using the following code in order to send 100 emails to myself:
declare @l_cnt int,
@l_subject varchar(100),
@l_minute varchar(5);
set @l_cnt = 1;
set @l_minute = convert(varchar(2),datepart(mi, getdate())) + ':' + convert(varchar(2),datepart(s, getdate()));
while @l_cnt <= 100
begin
set @l_subject = 'Email #'+ right('00'+convert(varchar(4), @l_cnt),3)+' group-'+@l_minute;
EXEC msdb.dbo.sp_send_dbmai
@profile_name = 'Mail_Profile',
@recipients = 'my.email@addr.com',
@body = 'nada',
@subject = @l_subject;
--waitfor delay '00:00:02';
set @l_cnt = @l_cnt+1;
end;
Thanks,
--=Chuck
July 31, 2016 at 7:59 am
Hi Chuck, I see you have no responses so I thought I'd throw some ideas in the pot.
There may be tweaks in Exchange to process the emails faster but this is probably not the right forum for finding the answer.
MSDN says delays in processing emails are from 'network interruption, a failure of the SMTP server, or an incorrectly configured Database Mail account', so you can investigate those.
In database mail, set the Logging Level to Verbose to see if you get more info.
Ideas for the original problem - (not tested - just ideas)
Do you send the same email to 300 people or are they different? If it's the same, then send one email and use a blind copy to mask all the addresses.
OR - Try sending chunks of emails, for example, send 20, then pause 4 secs (stick a loop counter in your code and reset it's value when you do the waitfor).
OR - Depending on what you send in the emails, if it's a query result, try and separate the processing of the queries from the sending of the emails. I don't have enough info on your business process to give further ideas on that part.
Good luck.
August 1, 2016 at 10:19 am
Each email is customized for the recipient, so they have to go out separately.
I'll try sending in blocks with a delay. SQL Server seems to be processing the first 50 or so without a re-send on those, so I think there's some opportunity there. Thanks 🙂
--=Chuck
August 5, 2016 at 11:04 am
I tried sending emails in blocks of n emails with m seconds between, and I thought I had it with 30 emails sent with a 60-second wait. But that only worked up to 100 emails. Once I tried sending 200 (and ultimately, I need to send 300+) I started seeing duplicates again. There were 27 duplicates in that pack of 200. So, I am just going to leave the original program as-is, with the 4-second wait between each email. It's not super-critical that it speed up, so we're not stuck or anything, it just would have been nice to get through production a bit quicker.
In any case, if someone else wants to tinker, here's the code using the modulo function to get the 60-second pause every block of 30 emails
declare @l_cnt int,
@l_subject varchar(100),
@l_minute varchar(5);
set @l_cnt = 1;
set @l_minute = convert(varchar(2),datepart(mi, getdate())) + ':' + convert(varchar(2),datepart(s, getdate()));
while @l_cnt <= 100
begin
set @l_subject = 'Email #'+ right('00'+convert(varchar(4), @l_cnt),3)+' group-'+@l_minute;
EXEC msdb.dbo.sp_send_dbmai
@profile_name = 'Mail_Profile',
@recipients = 'my.email@addr.com',
@body = 'nada',
@subject = @l_subject;
if @l_cnt % 30 = 0 --if @l_cnt/30 has a remainder of 0 then TRUE
begin
waitfor delay '00:01:00';
end
set @l_cnt = @l_cnt+1;
end;
--=Chuck
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply