Disabling Connection Pooling - ISerializable - Roy Osherove's Blog

Disabling Connection Pooling

ADO.NET Data Providers automatically use connection pooling turned on. If you want to turn this functionality off:

In an SQLConnection object, Add this to the connection string:

Pooling=False;

In An OLEDBConnection object, add this:

OLE DB Services=-4;

This way, the OLE DB data provider will mark your connection so that it does not participate in connection pooling.

P.S

This post was written using Scott's new Blogert.

It Rocks!

Published Monday, June 23, 2003 1:11 AM by RoyOsherove
Filed under: ,

Comments

Sunday, June 22, 2003 12:18 PM by James Avery

# re: Disabling Connection Pooling

Why would you want to disable connection pooling? I can't think of a reason and was curious why you are disabling it.

Thanks,
James
Sunday, June 22, 2003 7:22 PM by Roy Osherove

# re: Disabling Connection Pooling

Connection pooling will hold a connection open even though you specifically opted to close it, for X amount of seconds, then it closes it. There might be situations when you might want to have an "always-connected" application, in which case you would want to have complete control over your connections, and usually one that is open at all times.
Persoanlly I never needed diasbling yet, but , being the control freak that I am, When I see an "On by default" flag, it annoys me to not know hot to turn it off... :)
Wednesday, January 28, 2004 10:13 PM by Josh

# re: Disabling Connection Pooling

Thanks. I absolutely need it because when converting databases, the caching really mucks things up.
Monday, February 02, 2004 9:38 AM by Dave

# re: Disabling Connection Pooling

I'm disabeling OLE DB connection pooling because I want to get a view wether all our connections are being closed after useage... MS doesn't have OLE DB connection performance counters for this...
Friday, April 23, 2004 4:47 AM by Scott Cropley

# re: Disabling Connection Pooling

IN order to drop a database, all connections must be closed. As part of application set-up and maintenance, I frequently need to drop databases. You can't drop and recreate a merge subscription if connections are holding it open.
Tuesday, May 11, 2004 4:46 PM by Koutetsu

# re: Disabling Connection Pooling

If you're looking for an easy way to kill all open processes on a given database, try this:

Dim srv As New SQLDMO.SQLServer2
srv.LoginSecure = False
srv.Connect [server], [username], [password]

Dim res As QueryResults2
Set res = srv.EnumProcesses

Dim i As Integer, dbname As Integer, spid As Integer
For i = 1 To res.Columns
' Get the index of the dbname column
If res.ColumnName(i) = "dbname" Then dbname = i
If res.ColumnName(i) = "spid" Then spid = i
Next i

Dim rowcount As Integer: rowcount = 0
For i = 1 To res.Rows
If res.GetColumnString(i, dbname) = [database name] Then
srv.KillProcess res.GetColumnLong(i, spid)
rowcount = rowcount + 1
End If
Next i