Connection won't disappear from SQL Server until app closes

[Dave Sherrin]

I have no problems connecting to SQL Server 2000 from VB.net and using all
it's functions. However, when I close the connection, it stays active on
the server until the application is closed. I have tried running commands
with the drop connection parameter, closing the connection with close,
dispose and setting it to nothing. Here is a sample of the code. The
results of the code below is that the connection stays active. Is there a
setting in VB, ADO.NET or SQL Server I can tweak to fix this?

thanks in advance!

Dim myConnection As New SqlConnection("data
source=SRV-DC-MULTI1;database=Technical;Integrated Security=True")

myConnection.Open()

MessageBox.Show("ServerVersion: " & myConnection.ServerVersion &
ControlChars.Cr & _

"State: " & myConnection.State.ToString())

myConnection.Close()

myConnection.Dispose()

myConnection = Nothing

MsgBox("pause 2")

 

[William Ryan]

turn off connection pooling...
http://www.sql-server-performance.com/sql_server_performance_audit8.asp

 

[William \(Bill\) Vaughn]

This is by design. It's the way it's supposed to work. The connection will
eventually timeout (4-8 minutes) if not used. For client/server applications
than don't use connections very often, it's a good idea to turn off
connection pooling to free up the connection sooner. Consider that SQL
Server can handle thousands of connections without a problem. For an ASP
application do not turn off pooling.
It's not necessary to dispose a closed connection or to set it to nothing.

--
____________________________________
William (Bill) Vaughn
Author, Mentor, Consultant
MVP, hRD
www.betav.com
Please reply only to the newsgroup so that others can benefit.
This posting is provided "AS IS" with no warranties, and confers no rights.
__________________________________

 

[Bysza]

You can also customize pooling behaviour by editing your DSN.
Details can be found in .Net Framework Developer's Guide in article
"Connection Pooling for the .NET Framework Data Provider for SQL Server".

 

[William \(Bill\) Vaughn]

Huh?
The SqlClient (.NET Framework Data Provider for SQL Server) does not use nor
recognize DSNs. However, you can tune/enable/disable the Connection pool
using ConnectionString properties.

--
____________________________________
William (Bill) Vaughn
Author, Mentor, Consultant
MVP, hRD
www.betav.com
Please reply only to the newsgroup so that others can benefit.
This posting is provided "AS IS" with no warranties, and confers no rights.
__________________________________

 

[Angel Saenz-Badillos]

Dave,
The managed pooler for the SqlClient managed provider does not
currently provide a simple way to tweak this. Unused pooled
connections will stay in the pool for random(4 to 8) minutes before
they are automatically cleared.

For the complete story I would like to recommend Guy Smith-Ferrier´s
excellent
¨SqlClient Connection Pooling Exposed¨ article
http://www.sys-con.com/dotnet/article.cfm?id=483

It gives a very accurate description of how pooling was designed and a
possible workarround for this problem using reflection. Please note
that there are no guarantees that this workarround will work in future
versions of the framework since it involves private internal calls.

In the whidbey alpha released for the pdc we added methods ReleasePool
and ReleaseAllPools to the SqlConnection object that will allow you to
do what you are trying to do.

Thanks,
Angel Saenz Badillos Microsoft managed providers
This post is provided AS IS and confers no rights.