Hello. We had an issue this week where a user submitted the Sales Gross Margin report 4 times (it didn’t bring back results the first 3 times, since it was still running) and it locked up SSRS. This crippled our resources on the SQL server and EPICOR crashed.
We would like to know if there is a way to limit the number of active tasks from each user to 2. We understand that we could set a concurrent tasks limit in the task agent, but setting that to 2 seems like it would cause significant problems. If we could limit each user to only 2 active concurrent tasks, it could help prevent issues like this, where the other tasks would be held in Pending status.
How would you handle it if you had 50 sales people (not the same user) printing the report at the same time, let’s assume you grew to 800 employees.
I have mine set to 200 concurrent, no issues. Perhaps your Sales Gross Margin Report has a customization that is not efficiently implemented? #thoughts
Interesting… I have had some similar problems with V8/E9 in the past.
But have not run into this in E10… yet.
Can I assume the user specified all customers with a date range of the whole, past year? (and I’m guessing Customer Statements might behave the same)?
Do you know
how long the user waited between each of the report submissions?
how long the task were running before your systems started to bog down?
Not sure how this would translate to E10/SQL but…
In one environment I had some similar issues (V8/Progress)
Rather than try to control what users do/don’t do, I ended up scheduling a couple batch files to run every hour on the server
check the size of the “before image” - send an email when greater than some size.
check the size of some folder - again, send email if over some limit.
This worked “OK” for this site, since the tasks could run for a long time before server resources would become a problem. Even after I received an alert, I often waited 4 to 8 hours before cleaning things up.
You are correct, it was all customers for an entire year. The user only waited a minute or two between submissions, I think all 4 instances were submitted within about 5 minutes. It was very shortly thereafter that the SQL server was maxed out on RAM consumption and bouncing connections.
I am not sure that a batch routine would be of much help to us, as it would have to run every few minutes. Our volumes in SSRS are not all that great, so there are never jobs stacked up waiting to be processed. The idea was if we could easily limit each user to 2 active tasks at a time, even 2 full gross margin reports for all customers over 12 months wouldn’t be enough to stop the SQL server, but 4 is. Then the server could still function, users would just have to wait a little longer if submitting a whole batch of jobs.
The issue was that the user submitted the report for all customers for a 12 month period, and didn’t wait for the report to generate. The report was very large and has calculated fields, so it was taking SSRS longer than she is used to, since she usually runs the report for a single month. Being impatient prompted 3 more times of submitting the report instead of calling the helpdesk or waiting for a result. Normally we don’t have issues with SSRS and the server, or with this specific report.
The solution is to upgrade - I don’t know exactly what version they fixed this in, but now if you go to submit the same report again, it pops up and tells you to wait.
