Post reply

Controlling Scheduled Reports

  • April 11, 2011 at 7:56 am

    #249275

    I use Schedules to send out weekly reports. My weekly schedule runs about 7 reports (each report is a data subscription that sends out about 100 reports). That's about 700 reports. My problem is report server appears to be trying to run all 7 reports at the same time and is pegging out my CPU's and memory. I usually get several reports that do not run because of timeout issues.

    Is there any way to control how report server (or SQL Agent) queues these reports. Ideally, I would want each report to run only when the prior one completes.

    Thanks

  • April 11, 2011 at 8:54 am

    #1309874

    The first thing I would try is to simply schedule the report jobs so that they allow for time to execute.

    Report1 @ 7:00AM

    Report2 @ 7:15AM

    Report3 @ 7:30AM

    etc.

    If that won't work for you, disable the SQL Agent jobs for each individual base report so that they no longer run automatically. You can then either create another agent job that will execute each individual report job sequentially, or create an SSIS package that uses the Execute SQL task to execute each individual report job sequentially.

  • April 11, 2011 at 9:08 am

    #1309882

    thanks for the quick reply. What you described is kind of how I used to do it. But, the shear number of reports to schedule got out of hand. Right now, I do not have individual agent jobs for each report. I configured schedules under the Site settings of Report Manager. For example, I have a scheduled called "Weekly". When I setup my report distributions, I simply select this schedule. I may have 10 reports associated with this schedule and SQL agent only has one job (which is for the schedule as a whole). Makes changing the schedule easy as there is only one job, but managing the execution of reports associated with it a pain in the butt.

    I am using SSRS 2008 R2.

  • April 11, 2011 at 10:35 am

    #1309949

    I didn't stop to think that you might be using a shared schedule. You're right, that is problematic.

  • April 11, 2011 at 6:59 pm

    #1310179

    How about if you modify rsreportservice.config so that it only runs 1 subscription at a time, using MaxQueueThreads? (It says it's only for "subscriptions and notifications"...)

    http://technet.microsoft.com/en-us/library/ms157273.aspx

  • May 25, 2011 at 8:48 am

    #1329717

    Hi,

    There are a couple of things you could try.

    1. Review the reports and try and make them run more efficiently so that they do not time out. If the reports are running from live tables or views it may be a good idea to use custom tables, this should make the rpeort run a quicker.

    2. You could use cached reports so that when the subscription report run they do not stress the server.

    3. Increase the time-out for your reporting services instance - under site settings and time-out

    Thanks

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply