September 29, 2010 at 8:22 pm
Hi,
NOTE: The strange part (as mentioned in title) will come in the end.
I am running a SSRS 2008 report which fetches 3 million records from a remote server. After around 1 hour the report processing stops and I see an error icon on the lft bottom of the browser window. When I click on that to see the error details it shows some PageRequestManagerSQLErrorException with an unknown error message with code 12029 (sometimes 12002).
When I see the reportserver logs there is an error message logged in it which says "Microsoft.ReportingServices.Library.ReportServerDatabaseUnavailableException: The report server cannot open a connection to the report server database. A connection to the database is required for all requests and processing. ---> Microsoft.ReportingServices.Library.ReportServerDatabaseUnavailableException: The report server cannot open a connection to the report server database. A connection to the database is required for all requests and processing. ---> System.InvalidOperationException: Timeout expired. The timeout period elapsed prior to obtaining a connection from the pool. This may have occurred because all pooled connections were in use and max pool size was reached."
The <DatabaseQueryTimeOut> value in the report server configuration file is already having a value set to 7200 seconds(2 hours).
NOW, the strange part is, when I open the ExecutionLog2 table in ReportServer database, there is an entry for the same report with the status as "success" !!!
My head is spinning over this issue, somebody please rescue.
Regards.
September 29, 2010 at 8:48 pm
well, there's a few things going all wrong here;
the first one is such a large report ....three million rows.you've lost sight of what an HTML report is supposed to do.
it's supposed to present data in a format to review.
NO ONE can visually review 3 million rows. I would strongly recommend rethinking this report. it needs a lot more filters to prevent this kind of thing from happening. Also remember it's a web page, meaning that anyone waiting for more than say, 10 seconds is going to complain....you waited an hour plus, right? is that "acceptable" in any business you know of?
Second, 3 million rows in an HTML report will simply overwhelm the presentation layer, whether it's IE,FireFox or GoogleChrome; the data download might take a long time, but it's the attempt at rendering that is most likely killing you; creating 3 million x (number of columns) of fields, say 10 columns, is 30 million text boxes; Ie is dumping stuff to disk as it runs out of RAM, and chugging away, trying to build an html document that it cannot display unless you've got 32 gigs of ram.
it's going to depend on the machine that is running the report, but my decent 2Gig Ram machine has choked on a plain old html document, no dataconnection at all, just raw html generated to create a table, and with only 40K rows. try it yourself. create an html document with 40K rows or so, as a plain old table, and try to open it.
Any additional javascript only further complicates the file.
Third, a remote server is of course much slower than a local server. Is the remote server accessed via a linked server, or just the connectionstring for the report?
if you are joining a linked server to a local server, there is a known performance issue as the local server downloads all the rows form the linked server into tempdb, then performs joins/filtering.
Finally, 3 million rows of data takes time to actually move over the wire. what kind of connection is between the report server and the database server? that can slow things down.
Lowell
September 30, 2010 at 7:38 am
Thanks for the detailed reply.
First thing, I am doing performance analysis and trying to see how far my current configuration can go with huge SSRS 2008 reports. Till 2 million rows everything was fine. I am not running anything into production but doing research to have an optimal configuration before putting the reports in production. Why am I doing this because there are some business requirements but lets not go into those details.
My problem is with 3 million rows I am getting this connection timeout issue, but as I mentioned in the last line of my message how come executionlog2 table is showing an entry as report successful?
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply