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
So, I think theres something leaving stale connections on my ReportServer web service on ServerA. Has anyone seen this before?