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 -d ;pooling=false -a WINDOWS -u -p 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?

At the end of December last year I convinced my boss to buy a couple ssd’s for adaption testing for the next wave of hardware refreshes at my company.  Mine just failed last week out of the blue.  I logged of at work then turned my laptop on at home, and saw the “missing boot device” message.  It’s not recognized in any bios that I’ve tried.  RMA is ready, I just have to pack and ship it back.  The other drive we purchased failed two weeks in, so I’m not sure if the cheaper brands are ready for prime time.  Coupled with the fact that there is a distinct lack of data recovery options with ssd’s and I know they are not ready for the general population at my employer.