SSRS 2005 Data Driven Subscriptions

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?

2 thoughts on “SSRS 2005 Data Driven Subscriptions

  1. And an update. Today, I checked the Perfmon collection “.Net Data provider for Sql Server\NumberofNonPooledConnections” on ServerA and it was at 2000 by the end of business. However, the number of connections into SQL Server is not different than any other day, with anywhere from 50 to 80 connections. No memory or CPU changes that we have noticed. For now I’m just going to trace the connections counter into a file, and if it does crash at some point, I’ll have a record of it.

    Also, there have been 0 errors as shown above… a huge difference from before the change.

  2. And a further update. After capturing the NumberOfNonPooledConnections for the past week and a half, it’s a sawtooth, cycling from ~2300 down to 1 and then slowly backup up every 12 hours. That matched up perfectly with the ReportServer__xxxx___main trace file logging a recycle event. That matched up perfectly with the recycle setting in the RSServer config file at 720 minutes.

    Since the change every subscribed report has run without error and there has not been a single timeout logged in the trace file. So here’s my thoughts right now. When I changed the connection string to pooling=false via RSConfig.exe, it removed the upper limit on open ADO.NET connections from the Report Server to SQL Server. For some reason this installation of Report Server is not closing connections completely. They close in SQL Server, but stay stale in the OS. We’re running SQL Server 2005 Enterprise Edition with SP3 + MS09-062 build and matching Reporting Services. I think that if enough subscriptions are in the queue and the upper pool limit is reached between the 720 minute recycle setting, that’s when I was seeing the timeouts logged in the trace files. Then the EVENT table would fill up until the recycle was triggered and then it would start all over again, only with a bunch of requests queued up.

    Fortunately, before the end of the year we should be upgrading to SQL Server 2008 R2 and I plan on a clean install instead of an in-place upgrade. The only other thing I can think of that might be different than an out-of-the-box install is that this was originally a SQL Server 2005 Standard edition that I upgraded with the -SKUUPGRADE flag.

    Interestingly enough we have a staging environment that almost exactly mirrors production. I have just copied the report subscriptions to the stage instance, and changed the email address to send them all to me. This instance has the default settings in the connection string, so I want to see if I can reproduce this.

Leave a Reply

Your email address will not be published. Required fields are marked *

*

This site uses Akismet to reduce spam. Learn how your comment data is processed.