VB.Net SQLClient Connections and SQL Server - active connections reflect even though all closed

Question

Using VB.Net and SQL Server 2008 R2:

I have recently had a database that using sp_who2 reflects a connection ".Net SqlClient Data Provider" is still active even though the .Net application has closed and disposed of all connections.

The code looks like this:

Imports System.Data.SqlClient

Private Sub TestSQLConnection()

    Dim strConnection As String
    strConnection = "Server=MyServer;UID=User;PWD=Password;Database=MyDatabase"


    Dim conn As New SqlConnection(strConnection)
    conn.Open()

    conn.Close()
    conn.Dispose()

    GC.Collect()
    GC.WaitForPendingFinalizers()

End Sub

And when i look at SQL Server usng sp_who2 after running the application (look at the bold .NetSQLClientDataProvider)

SPID Status Login HostName BlkBy DBName Command CPUTime DiskIO LastBatch ProgramName SPID REQUESTID

57 sleeping sa YOUR-PC . master AWAITING COMMAND 0 0 07/19 12:38:48 *.Net SqlClient Data Provider* 57 0

Only when i actually close the APPLICATION will the .Net SQLClient Data connection be removed from the view. What i am concerned about is the following: 1. what affect are these process's having on my database from a resource perspective and why is the connection reflected and sleeping. 2. When i try detach a database i se that 1 connection is active but the code has closed and disposed.

Answer 1

It's called Connection Pooling. The expectation is that if you've opened a particular connection, you're likely to want to use that connection again soon (unless, as you say, you actually close the application), so although you close and/or dispose the SqlConnection object, the actual network connection to SQL Server is kept open behind the scenes.

You can instruct the connection provider to not perform connection pooling, but it's usually a good thing to keep enabled. To disable it, add Pooling=false as an option in the connection string.

You can also force existing connections to be closed in the pool. My first link includes this information:

ADO.NET 2.0 introduced two new methods to clear the pool: ClearAllPools and ClearPool. ClearAllPools clears the connection pools for a given provider, and ClearPool clears the connection pool that is associated with a specific connection. If there are connections being used at the time of the call, they are marked appropriately. When they are closed, they are discarded instead of being returned to the pool.