1/12/2012

Too Many Connections - SSRS DDS

We have an SSRS report with a Data Driven Subscription.

We process 7000+ email notifications every week around 9am same day.

Recently a bunch of people became vocal about not receiving email notifications.

So I checked the Configuration File:


<Extension Name="Report Server Email" Type="Microsoft.ReportingServices.EmailDeliveryProvider.EmailProvider,ReportingServicesEmailDeliveryProvider">
                        <MaxRetries>3MaxRetries>
                        <SecondsBeforeRetry>900SecondsBeforeRetry>
                        <Configuration>



So I checked the ExecutionLog table in the SSRS Server look for max retries = 4 because we have a server configuration set to 3:




select
      C.path
      ,convert(varchar(40),parameters)
      ,convert(varchar(10),TIMESTART) daterun
      ,count(*) countrun
from         reportserver.dbo.ExecutionLog E
            join reportserver.dbo.catalog C on E.reportid = C.itemid
where
convert(varchar(1000),c.path) = '/RPT FOLDER/RPT NAME'
group by
      C.path,
      convert(varchar(40),parameters),
      convert(varchar(10),TIMESTART)
having count(*) =4

order by 1,4 desc,3 desc ,2

This provided a list of people who did not get their emails...



Then checked the log files on the SSRS Server for errors --> Found this:


ReportingServicesService!emailextension!23!01/11/2012-09:00:18:: Error sending email. System.Runtime.InteropServices.COMException (0x80040211): The message could not be sent to the SMTP server. The transport error code was 0x800ccc67. The server response was 421 #4.4.5 Too many connections from your host.

So I spoke with the IT people who manage the mail server and they opened up the emails from 50 to 5000 concurrent - we'll check next week and see if the issue has been resolved.

Also, I posted this to Twitter and

Vijay Krishnan

@vijayreports India

happily responded with this link:

http://msdn.microsoft.com/en-us/library/ms345234.aspx



 Vijay Krishnan 

@ 
 if  creates eml in local pickup folder with local smtp per  setup smtp forwarding on local smtp service?

 Vijay Krishnan 

@ 

 N of course backup  config files/ encryption key/ databases before any change, if being considered
 Vijay Krishnan 

@ 
 Sure sounds SMTP limits are at play; using local SMTP if 7000 emls got created it would have got stuck in SMTP forwarding

And another response from:

Adam Saxton


Ft. Worth, TX

 Adam Saxton 

@ 
  Local pickup for mail is good, just be aware  will show that it was sent, even if it fails later by mail server


Vijay Krishnan
@
Good catch, Adam! SMTP log is then needed. Hopefully, the current changes to the remote smtp help shovel the mail snow :)

Thanks Vijay and Adam!!

And


Happy Reporting!!!

No comments:

Post a Comment

Note: Only a member of this blog may post a comment.