I have an interesting set up for Reporting Services Data Drive Subscriptions. We have two database servers, one which has the SQL Server Enterprise license. This server is contains our transactional databases - ServerA. ServerB is our Reporting databases. We have Reporting Services installed on ServerA to use the Data Driven Subscriptions, but all the report Datasources for the reports point to ServerB. Actually our users cannot directly access ServerA, and we have a web front end that allows users to subscribe to canned reports. This stores the needed report information in a table on ServerB. Reporting Services from ServerA polls this table every 10 seconds and enters the subscribed jobs into the event table in the ReportServer database on ServerA. We often have entries in our ReportServer trace files showing ReportingServicesService!library!14!06/21/2011-13:25:04:: e ERROR: Throwing Microsoft.ReportingServices.Diagnostics.Utilities.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., ; Info: Microsoft.ReportingServices.Diagnostics.Utilities.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. at System.Data.ProviderBase.DbConnectionFactory.GetConnection(DbConnection owningConnection) at System.Data.ProviderBase.DbConnectionClosed.OpenConnection(DbConnection outerConnection, DbConnectionFactory connectionFactory) at System.Data.SqlClient.SqlConnection.Open() at Microsoft.ReportingServices.Library.ConnectionManager.OpenConnection() --- End of inner exception stack trace --- When I changed the default max pool from 100 to 150, and started perfmon for the .NET Data Provider for SQL Server - Number of Pooled connections, I would see it slowly and steadily rise to 150, and then the above error would be logged, and then generally the event table would start filling up with entries. Today I have changed the pooling - using RSConfig.exe -c -s <SERVER> -d <DATABASE>;pooling=false -a WINDOWS -u <USER> -p <PASSWORD> as a test, I now notice some interesting results in the perfmon counter. The counter is constantly stepping up, the counter "Number of NonPooled Connections" is over 600 now, but in SQL Server, I see the number of connections via SP_WHO2 to be much lower, averaging around 10-40 at any given time. Remember, all the report data sources are pointing to ServerB, and I'm monitoring the .NET Data provider for Sql Server on ServerA. SP_WHO2 on ServerB, general has no connections from ServerA with the user configured in the DataSources, unless a report is active. So, I think theres something leaving stale connections on my ReportServer web service on ServerA. Has anyone seen this before?

Hi Jason, Thank you for your post. Since the scenario is complicated, I will try to involve someone familiar with this topic to further look at this issue. This might be some time delay. Appreciate your patience and understanding. Regards, Tony ChainTony Chain [MSFT CSG] | Microsoft Community Support Get or Request Code Sample from Microsoft Please remember to mark the replies as answers if they help and unmark them if they provide no help.

Tony, Thank you... I have nothing but patience with this issue as its a slowly smoldering problem :) I will say that as of today, the .NET Data Provider for SQL Server - Number of Pooled connections counter on ServerA has topped 2000 connections, with SQL Server SP_WHO2 not showing a deviation in the average number of connections at any given time of the day.Jason Fay Sr SQL Server DBA Joy Global Inc

And a follow up. The perfmon report for the counter is a sawtooth, with it cycling down to 0 every 24 hours. It is cleaning itself up, and there appears to be no performance impact. So far in the past week, all of the subscribed reports have fired off on schedule and we have received no errors in the Report Server trace files. Jason Fay Sr SQL Server DBA Joy Global Inc

Hello Jason, So at one point you were reaching maxed pooled connections on server A and now that is not the case? On your sawtooth comment, this could be due to the app domain recycling. Check your Reportserver config file under "RecycleTime". The default is 720 (12 hours). It may have been doubled for some reason. This could also be due to an application pool recycle. If this occurs all connections would be reclaimed. Check your app pool settings to see if it is set to recycle at a certain time or a certain memory threshold. It seems strange it would occur at the 24 hour mark each time, sounds like a setting. At any rate looks like you have been running solid for a week, I am glad to hear that. ThanksTerrell An -MSFT

Terrell, I also found it odd to be recycling every 24 hours. The Application Pool in IIS is set to recycle every 1740 minutes or every 29 hours. The other limits are set at 0 (private memory, requests, etc). Here's the perfmon data I have. Jun 22nd, 1:44:26 PM it was 2290 connections, two seconds later it was 1. June 23rd 1:44:28 AM it was 2306 connections, two seconds later it was 1. June 23rd 1:44:30 PM it was 2302 connections, two seconds later it was 1. June 24th 1:44:31 AM it was 2306 connection, two seconds later it was 1. June 24th 1:44:32 PM it was 2302 connections, two seconds later it was 1. So, it's not every 24 hours, but very much closer to 12 hours. Sorry, with 3 days of collected data, I was not seeing the dates right in the Permon viewer. I decided after doing the above to look at the trace files... it matches up... From the ReportServer Service Main traces... ReportingServicesService!servicecontroller!9!6/23/2011-01:44:28:: i INFO: Recycling the service from default domain ReportingServicesService!servicecontroller!9!6/23/2011-01:44:29:: i INFO: New app domain started ReportingServicesService!servicecontroller!9!6/23/2011-13:44:30:: i INFO: Recycling the service from default domain ReportingServicesService!servicecontroller!9!6/23/2011-13:44:30:: i INFO: New app domain started ReportingServicesService!servicecontroller!9!6/22/2011-01:44:24:: i INFO: Recycling the service from default domain ReportingServicesService!servicecontroller!9!6/22/2011-01:44:26:: i INFO: New app domain started ReportingServicesService!servicecontroller!9!6/22/2011-13:44:26:: i INFO: Recycling the service from default domain ReportingServicesService!servicecontroller!9!6/22/2011-13:44:27:: i INFO: New app domain started ReportingServicesService!servicecontroller!9!6/22/2011-01:44:24:: i INFO: Recycling the service from default domain ReportingServicesService!servicecontroller!9!6/22/2011-01:44:26:: i INFO: New app domain started ReportingServicesService!servicecontroller!9!6/22/2011-13:44:26:: i INFO: Recycling the service from default domain ReportingServicesService!servicecontroller!9!6/22/2011-13:44:27:: i INFO: New app domain started So it's not the IIS Worker Pool recycle, but the RSReportServer.config value <RecycleTime>720</RecycleTime> that's recycling all the connections. Is there a bug in SQL Server 2005 build 4035 (sp3 + MS09-062 fix), with Reporting Services database version C.0.8.54 with regards to keeping stale connections on the OS side?Jason Fay Sr SQL Server DBA Joy Global Inc

Jason, No bugs of that nature to my knowledge. The counters you are looking at are probably a running count of the connects that were made during that time preriod. Like you said, you don't see that many connections through SP who. Like an hour before your app domain recycle, start a new perfmon, the numbers will likely not be that high. Are you still getting the Max pooled connection error or has that gone away? ThanksTerrell An -MSFT

Yup, I've seen it (rather often over the last decade or two). Basically, I expect that the routine that's polling the server is not properly closing the connection. This orphans the connection in the pool. When the App domain restarts (or the server restarts) the pool is discarded and you start over again. Go back to your application and make sure you have a Try/Catch/Finally. Make sure that the Catch is trapping the exceptions and (most importantly) the Finally closes the connection. Never depend on the garbage collector to clean up the connections. And, (humor me) if you find that you're using a "using" block to manage the Connection object state, replace it with explicit Close method calls instead. hth__________________________________________________________________ William Vaughn Author, Mentor, Trainer, MVP Beta V Corporation William Vaughn's blog Hitchhikers Guide to Visual Studio and SQL Server (7th Edition) The Owl Wrangler a fantasy fiction novel Please click the Mark as Answer button if a post solves your problem!