How to force a SqlConnection to physically close, while using connection pooling?

Question

I understand that if I instantiate a SqlConnection object, I am really grabbing a connection from a connection pool. When I call Open(), it will open the connection. If I call the Close() or Dispose() method on that SqlConnection object, it is returned to the connection pool.

However, that doesn't really tell me if it's really closed, or if I still have an active connection to the database.

How can I force a SqlConnection to close at the network level, or at least tell when it closes?

Example:

using(SqlConnection conn = new SqlConnection(DBConnString)) {

   conn.Open();
   SqlCommand cmd = conn.CreateCommand();
   ...
   cmd.ExecuteReader(CommandBehavior.CloseConnection);
   ...
}

• First run: 300 ms
• Second run: 100 ms
• Third run: 100 ms
• After waiting a long time (30 minutes): 300 ms

If the connection was TRULY closing, the second and third runs should also be 300 ms. But I know that the connection is not truly closed for those runs (I checked the SQL Server's activity monitor). It doesn't take the extra 200ms to perform authentication/etc.

How do I force the connection to truly close?

Ideas

• Does CommandBehavior.CloseConnection work? (apparently not?)
• Does setting "Max Pool Size = 0" in the connection string work? (this would be a pyrrhic solution)
• Does Dispose() work?

References

• Article on Connection Pooling
• Here's another one that tells us that Close() doesn't really close the connection.
• An article on pros and cons connection pooling

Allistir

why connection truly close ? low performance ? – Comer

Answer 1

Maybe SqlConnection.ClearPool ?

Tocopherol

I'm doing this, but my SQL connection still shows up in sp_who2 and my SQL Session-held AppLocks remain locked (I intend them to die with the connection). I even set connStrBuilder.Polling = false. – Conceptionconceptual

Answer 2

Moe Sisko's answer (Call SqlConnection.ClearPool) is correct.

Sometimes you need a connection to really close rather than return to the pool. As an example, I have a unit test that creates a scratch database, builds the schema, tests some stuff, then drops the scratch database if the tests all pass.

When connection pooling is active, the drop database command fails because there are still active connections. From the point of view of programmer all SQLConnections are closed, but as the pool still holds one open, SQL Server won't allow the drop.

The best documentation for how connection pooling is handled is this page on SQL Server Connection Pooling on MSDN. One doesn't want to turn connection pooling off entirely because it improves performance with repeated opens and closes, but sometimes you need to call a "force close" on an SQLConnection so that it will let go of the database.

This is done with ClearPool. If you call SqlConnection.ClearPool(connection) before closing/disposing, when you do close/dispose it will really go away.

Coir

The use case you describe is almost exactly what I was doing - testing performance of certain db access patterns, using "cold" procedure/data caches and of course, connections. – Allistir

The link you provide has been added to the question text. Thanks. – Allistir

Same scenario for me :-) – Polemic

You can solve the problem ("When connection pooling is active, the drop database command fails because there are still active connections") by issuing use master; before closing your connection. It doesn't answer OPs question, but it solves your problem. – Debauchee

Answer 3

If you don't want to use the connection pool you have to specify it in your SqlConnection.ConnectionString property. For example

"Data Source=MSSQL1;Database=AdventureWorks;Integrated Security=true;Pooling=false;"

Disposing or closing the SqlConnection object is just going to close the connection and return it to the connection pool.

Impotent

Answer 4

Generally, you want the connection pool to do its job - you don't want the connection to truly close.

Why specifically do you want the connection not to return to the pool?

Cramfull

A connection in the pool can will hold locks. – Debauchee

I'm not sure what kind of locks you're referring too - a disposed connection whose underlying connection is returned to the connection pool should not be participating in any transactions or holding any locks relevant to consuming code. Of course it's still using a network port and a few other resources - but the very acquisition cost of these resources is why you don't what to free them. Unless you're trying to delete/restore the underlying database or something similarly radical, these locks aren't problematic. – Cramfull

A connection in the pool can prevent exclusive access to a database. – Turcotte

Answer 5

I see that you are using .net but as this showed up in a google query allow me to give a java response...

Use a DataSource that implements Closeable() and call close on the DataSource. Hikari supports Closeable.

Sharpe

Answer 6

CommandBehavior.CloseConnection is usually discouraged because of this very fact - You can't be sure that the Connection will be closed. (I'll try to find some concrete evidence of this, I'm saying this from faint recall).

Dispose() is the surest way because it implicitly calls Close().

The using construct demonstrated by @Alex is just another (programmer friendly) way of writing the try-finally construct with the added implicit Disposal of objects.

Edit: (after edit to question)

Your concern over the connections actually closing seems unwarranted to me. The connection would simply return to the pool so that it can be reused easily without having to go through all the initialization. This does not mean that the Connection is still actively connected to the DB.

Poisonous

A connection in the pool does mean it is still activly connected to the DB. For example you can close a Connection object, and you'll still have a shard database lock due to the connection still being in the pool (#521216) – Debauchee

Answer 7

Robert's answer of SqlConnection.ClearPool(TheSqlConn) did exactly what I wanted. It's nice to know the pool CAN be interacted with when necessary.

My use case was: We have ruined a connection and let it go back into the pool, how to we detect that it's ruined and refresh it, so the next user won't have problems.

The solution was: Detect that we have just ruined the connection, and clear it from the pool entirely, letting the pool fill back up with fresh connections.

A decade of writing SqlClient.SqlConnection and I never even thought of interacting with the pool till today.

Tilton