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.

Although I agree that the error reporting in this case may be confusing or inaccurate, the fact remains that you are clearly stressing the resources of your server and/or the SSRS platform. There are limits to any tool and it looks like you've found one of them. Since it's a time-out problem, my best guess is that the issue is with the data provider or data processing extension rather than the rendering engine. If the data were aggregated at the source, it might work better. This reminds me of a post to the MS Access support forum way back in the day. Access didn't have a stated maximum number of pages for a single report and the forum poster was complaining the the application threw and error when he exceeded 32,767 pages. I think the answer to your issue is the same as the answer I gave back then. It's like the guy who goes to the doctor and says "Hey, doc, when I do THIS it hurts; and the doctor says, "don't do THAT." For what ever reason, Reporting Services doesn't like you running 2 hour reports with 3 million records. I don't know what the business case might be for running a report that size but this is probably not a good idea - certainly not what the product team had in mind when they architected the platform. My suggestion is that we look at your business requirements and try to find a better solution to get you there. Please describe your reporting scenario and your objectives for aggregating or presenting this data. Paul Turley, MVP [Hitachi Consulting] SQLServerBIBlog.com

Hi Paul, Thanks for the reply. Actually there might be only few reports (probably 2 ) that might fetch upto 3 million records. The business case is simple that if user wants to see all rows for a particular key column. So this is not actually in production yet, but this is a research in trying to infer how far we can go with our current configuration (Win Server 2003 R2 SP2 64 bit, SQL Server 2008 R2 64 bit Ent. edition, 8GB RAM). So I am doing performance analysis by running some test reports. These reports contain a simple select query that selects all records from a table which has 3 million rows. The data source is a remote server, SQL Server 2005 32 bit Ent. edition. I agree that such a huge report means stressing my resources but then why am I seeing an entry for the 3 million records report in my executionlog table with status as "Success" if it is not rendering and giving this error? This is bothering me right now. Regards.

Not sure if this will help but you might give it a try : 1. Open the Registry Editor. 2. Navigate to the registry path below. HKEY_LOCAL_MACHINE\System\CurrentControlSet\Services\Tcpip\Parameters\ 3. Create a DWORD value MaxUserPort. Value name MaxUserPort Value data (in Decimal) 10000 4. Restart the server. 5. Check and confirm that the registry key ‘MaxUserPort’ is added. Do the same process as above for TCPTimedWaitDelay and reduce the value to 60. The above steps are to increase the maxuserports and reduce the time_wait in order to free up resources on the server.

Hi, I have figured it out, because of the huge data size the connection from the source data side was getitng timed out, so I increased the connection timeout value at source side and now I can see a report even with 4 million records (though it takes around an hour to render for obvious reasons). While connecting to source data through SSMS, in the connection dialog box, click on options to expand it and increase the timeout value. Regards. Thanks for your posts.Sachin Vaidya as SQLearner

Sach I can see where the log entry may be confusing. I've refered your post to a member of the product team as this may be of interest. You you think think this may be a bug, please post it to the Microsoft Connect site at https://connect.microsoft.com/ for SQL Server. Thank you for letting us know.Paul Turley, MVP [Hitachi Consulting] SQLServerBIBlog.com

Hi Sach, How could increasing timeout value fixed that issue ? This value is just to wait for a connection to be established before timing out. (As refered in below article.) http://msdn.microsoft.com/en-us/library/ms180213(v=sql.105).aspx Actually, I am also facing this strange issue and looking for a permanent solution :)