The timeout period elapsed prior to obtaining a connection from the pool - but the pool is not full

Question

I hate even bringing this up b/c there are so many hundreds of pages talking about this error dating back 7-8 years... However, I cant quite figure this out.

I have a report service running on server A which connects to SQL Server 2008 R2 on Server B. Both servers are running Windows Server 2008. The report service is multi-threaded and runs up to 10 reports at a time each on its own thread.
My connection string is as follows:

Data Source=ServerB;Initial Catalog=DBName;trusted_connection=YES;Connection Timeout=0;Max Pool Size=500;

As you see, I have set the connect timeout to 0 to wait for a connection until its available, and max pool size to 500 b/c I wanted to make sure the pool was not too small for my multi-threaded service. The error typically only shows up when the service starts and there is a queue of reports waiting to be processed.

So obviously, the first thing to do is look for leaking connections.. there have to be connections that are not being closed that are causing the pool to fill up, right? Nope -looks like the pool never fills up.

I have spent the last several hours running Performance Monitor on the server watching ".NET Data Provider for SqlServer" and the NumberOfPooledConnections never gets above 20 or so. I also have perfmon running on the SQL server watching "SQLServer:General Statistics" and UserConnections never goes above 50. During this time, I have caused this error to be reported 2 or 3 times by manually restarting the service.

What else can I look for? What else would cause this error? Full error message is:

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()

Thanks, Stephen

Solution

This is known bug as shown here

Posted by Microsoft on 8/15/2008 at 11:31 AM Thanks for your feedback. The fix was submitted to the source branch of the next major .Net release.

Comments

[Stephen]

Thanks, VMAtm. Changing the Connect_Timeout from 0 to 300 seems to have fixed it.

[sharptooth]

The link yields an HTTP 404.

[VMAtm]

@sharptooth Microsoft clears out their feddbacks as they post the fixes. I think fix already been published.

[sharptooth]

That's weird, they usually just mark it "fixed" and it stays there.