SQL Server connection pool doesn't detect closed connections?

Question

For years, I've experienced very weird problems on all my web applications that connect to a SQL server.

The problem is that if something happens to the database server (server restart or other problem), de web app stops working from that point on, even if the database server is alive and well afterwards.

What happens is that every ADO.NET operation (ExecuteNonQuery, CreateReader, BeginTransaction, ...) fails with a InvalidOperationException: "Invalid operation. The connection is closed". It seems that a call to SqlConnection.Open() retrieves a connection from the application pool which is... closed!

According to the documentation, the connection pool should automatically remove severed connections from the connection pool, but apparantly a closed connection isn't regarded as "severed", so the call to SqlConnection.Open() happily returns a closed connection, assuming it is open, without checking this.

My current workaround is to check for the state of the connection right after opening it:

using (SqlConnection connection = new SqlConnection( connectionString ))
{
   connection.Open();

   if (connection.State != ConnectionState.Open)
   {
      SqlConnection.ClearAllPools();

      connection.Open();
   }

   // ...
}

This workaround seems to work for now, but I don't feel comfortable doing this.

So my questions are:

1. Why does SqlConnection.Open() return closed connections from the connection pool?
2. Is my workaround valid?
3. Is there a better way to handle this?

Rebane

I've found that you can get severed connections just in normal usage, without SQL restarting. You end up with "ExecuteNonQuery requires an open and available Connection. The connection's current state is closed." I deal with over 10,000 databases, so I always disable connection pooling. It's never worked right for me if I have more than one database. – Mcmahan

Answer 1

I did some similar research into connection pooling a while ago, for a slightly different reason, but hopefully will be of some use. What I found is:

1. even when you close a connection in code, it is returned to the pool without the connection actually being closed - ready for further use.
2. if that connection gets severed (i.e. SQL Server restarts), when the connection is returned from the pool for another caller to use and that caller does a .Open on it, it does not error at that point when the database server is still down. This is part of the performance benefit of connection pooling as it's not actually going back off to the database server to connect.
3. when you actually try to execute a command against the connection (e.g. ExecuteNonQuery) it is at that point it actually throws an exception

Connections are automatically removed from the pool, my findings were that this typically occurred within a few minutes after it was last used. So, it may be a timing issue - and they are being cleared up, but not before the connections are attempted to be reused again.

These were some articles I looked at, at the time:
Sql Server Google Group
Using Connection Pooling in ASP.NET

Edit:
It does sound odd that the bad connection stays in the pool forever - are you sure it definitely does, and it's not just multiple bad connections? If you are sure then it sounds like those connections aren't being released properly within your code. This is another very good article I read a while ago, that says (quote):

Automatically Flushing Connections

If a pooled connection remains in the “closed but reusable” state for between 4 and 8 minutes (an interval chosen at random) the connection pooling mechanism closes the physical connection and discards the pooled connection. That is unless the number of remaining connections is greater than the minimum connections configured for the pool (the default is 0). Note that a connection must have been closed by the application (and released back to the pool) before it can be subject to automatic release. If you don’t close the connection in code or orphan the Connection object, the pooling mechanism will do nothing. No, there are no ConnectionString arguments to change the timeout value.

Oldtime

I could live with "a few minutes", but when the connection is used, it obviously throws an exception, but that "bad" connection remains available in the connection pool forever. It is never removed, although ADO.NET did throw an exception because the connection was bad. – Rebane

Answer 2

We've seen the same problem from C++ using ADO. A few years ago, after working with Microsoft Support, we also implemented similar retry logic in the code and reset the connection pool which resolved the problem.

If there is a better workaround the folks at Microsoft Support either didn't know it, or weren't sharing (At that time anyways).

Juneberry

Answer 3

i was getting random exceptions with firebird 3.

• dataadapterfill return 0 item.
• servertype not matching.

i lost 2 half-days while chasing down random errors .

exceptions does't even say the connection closed.

the fix was to create new connection every time. never use same connection.

//define function
fbconnection newfbcon()
{
    return new fbconnection("constr-here");
}

usage

  newfbcon().dataadapter("selecte x from table");
   

Festival