What can cause SqlClient to reuse invalid connections?

Question

We have a WCF service hosted in IIS, running on a server. The service connects to a SQL Server instance running on another server. In our code, we open and close the connection for each request. The WCF service makes several requests per minute to the database.

If I restart the SQL Service without stopping the WCF service, the WCF service starts logging errors to our error log, which is perfectly normal.

The problem is that sometimes (not always), after the SQL service has restarted, the WCF service continues to reports this error on every request:

Cannot open database "mydatabase" requested by the login. The login failed." 

The stack trace of the error ends with:

at System.Data.ProviderBase.DbConnectionPool.GetConnection(DbConnection owningObject)
at System.Data.ProviderBase.DbConnectionFactory.GetConnection(DbConnection owningConnection)
at System.Data.ProviderBase.DbConnectionClosed.OpenConnection(DbConnection outerConnection, DbConnectionFactory connectionFactory)
at System.Data.SqlClient.SqlConnection.Open()

While this is happening, I am able to connect to the database using SQL Management Studio (from the same machine running the WCF service). To fix the problem, I have to recycle the application pool for my WCF service.

So my question is: What can cause this and what can I do to ensure my app can recover from a SQL restart?

P.S.: On my dev box (also running IIS), when I do the same test I get the same error a few times (while the database is begin loaded I suppose) and then it starts working again.

Answer 1

Sometimes the connection you obtain from the pool is a new one (and your connection attempt succeeds), other times a connection is reused and the connection attempt fails. Not all severed connections actually get reused, because the pooler may or may not detect the condition and remove such a connection from the pool.

From here:

If a connection exists to a server that has disappeared, it is possible for this connection to be drawn from the pool even if the connection pooler has not detected the severed connection and marked it as invalid. When this occurs, an exception is generated. However, you must still close the connection in order to release it back into the pool.

If you create connections very rarely, so that there is no performance implication, it is best to turn pooling off. Just set Pooling to no in the connection string.