1

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.

Raphael Segal
  • 11
  • 1
  • 2

1 Answers1

3

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.

Damien_The_Unbeliever
  • 234,701
  • 27
  • 340
  • 448
  • 2
    @Smudge202 - so you're saying my answer is incorrect? And your search works because *you know that the phrase you're searching for* is "connection pooling" – Damien_The_Unbeliever Jul 19 '11 at 12:41
  • Damien great answer you should be the unbelievable and works like a charm many thanks. i searched the archives but couldnt put it together thanks a bunch!! – Raphael Segal Jul 19 '11 at 12:45
  • @Damien - I haven't said your answer is correct or incorrect. You and I both know this is due to the connection pool. This question differs from others possibly only because of the inclusion of sp-who2, so the question itself isn't an _exact duplicate_. But why do we need copies of the same answer for what boils down to the same question? Maybe I don't understand the site correctly... – Smudge202 Jul 19 '11 at 12:49