0

Weird thing happened the other day. Around 2 am there are lots of subscriptions fires on report server that sends emails. Users received some of them, but not all. Then around noon, all of the sudden users started to receive bunch of emails.

It turned out those were emails that supposed to be delivered at 2 am, however, the delay took around 10 hours (till noon).

It seems like subscriptions did the job and fired emails, but those emails were queued somewhere for such long period of time, then delivered all at the same time.

I looked at ExecutionLog1, 2 and 3. Looks all normal. Between start and end only 3 seconds.

enter image description here

Agent job also fine. Finished successfully at 2 am.

What could cause such a huge delay in email delivery?

It happened on Monday, which can be busy but not as busy as it would be at the end of the month.

Where else should I look for a reason?

Improve this question
2
  • Anything on SQL Server log around the time the emails should have been delivered? What's the Retry mail delivery configured for that server?
    – Ronaldo
    Commented May 11, 2021 at 23:30
  • You should check your mail server to eliminate it as a potential source of the issue. I've seen Microsoft Exchange servers rate limit even internal emails and have experienced somewhat similar behavior resultantly.
    – J.D.
    Commented May 11, 2021 at 23:55

1 Answer 1

Reset to default
0

I'm currently looking into this as we've had delays of about 30 minutes to subscriptions which have historically run without issue.

According to MS & other materials online SSRS generates rows in Events and Notification tables in your SSRS DB (https://businesswintelligence.com/content/69/ssrs-subscription-late-stuck-in-pending). Then the number of threads it can use to process those rows depends on the number of cores x 2 you have on the machine running your SSRS instance by default (your RS config can be changed to increase this, research this first though). So if there's a long running data driven subscription prior or if you simply have so many rows relative to the number of cores x 2 your machine has and it could take additional time for SSRS to get around to processing that Notification row.

This means that the subscription SQL Agent Job could fire off at 2am (as you stated) but SSRS doesn't get around to actually processing it in the notifications table until some time later.

Not ideal.

Improve this answer

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.