connection in connection pool with pooling=false

[Jason Collins]

I'm finding that even when pooling=false, a connection remains in my
connection pool connected to my database (it seems to quite quickly timeout
though - say 30 seconds).

My connection string:

Initial Catalog=Benji_UnitTest;Data Source=.;Integrated
Security=SSPI;Pooling=false



Using this setting, I would expect that a new connection be created
everytime, and that when the SqlConnection is close()'d (i.e., returned to
the "pool") that the underlying database connection would be closed.

I also tried setting ";Connection Lifetime=1" in order to get the connection
to close quickly (as a worksround); this didn't seem to affect any change.

Am I misunderstanding something?



Thanks,

j

 

[Kevin Yu [MSFT]]

Hi Jason,

First of all, I would like to confirm my understanding of your issue. From
your description, I understand that there is always one connection in the
connection pool after you have closed the connection explicitly and set
pooling=false in the connection string. If there is any misunderstanding,
please feel free to let me know.

As far as I know, the Connection Lifetime property only works when pooling
property is set to true. When a connection is returned to the pool, its
creation time is compared with the current time, and the connection is
destroyed if that time span (in seconds) exceeds the value specified by
Connection Lifetime. So in this case, the Connection Lifetime property
doesn't take effect.

Could you tell me from where you can see there is still a connection remain
in the connection pool? Please also check if there is any other connection
objects with the same connection string open and disconnect all other
programs that might connect to the database, such as SQL Server Enterprise
Manager.

HTH.

Kevin Yu
=======
"This posting is provided "AS IS" with no warranties, and confers no
rights."

 

[Jason Collins]

I can see the connection remaining in Enterprise
Manager->Management->Current Activity->Process Info.

I know that it is from that connection pool, because the username is
indicated, and this connection pool is the only thing that uses that
username.

j

 

[Kevin Yu [MSFT]]

Hi Jason,

Based on my research, there will always be a process in the list if you are
using Enterprise Manager to check for connection. Because when you are
check the processes, you are connecting to the server. So the process is
created by Enterprise Manager, not by the connection pool.

HTH.

Kevin Yu
=======
"This posting is provided "AS IS" with no warranties, and confers no
rights."

 

[Jason Collins]

Not under that username though. The Enterprise Manager connection will be
under my username (I am connecting with Enterprise Manager using Integrated
Windows Security).

There are lots of connections in Enterprise Manager - this is normal.
However, even with "pooling=false", there is a connection from my
application (which goes away after 20-30 seconds). I KNOW it's from my
application because that username is ONLY used in the connection pool
specified for that application.

So, if "pooling=false", why does the connection pool maintain a connection
(that times out in 20-30 seconds)? Is it an administrative connection or
something? Can it be turned off?

Thanks,
j

 

[Patrice]

I don't see this behavior (.NET 1.1). I tried a windows app that connects to
another SQL Server box with pooling disabled. I see a single connection that
is closed when the connection is closed client side.

Is your SQL Server on the same machine ? What if you try from a distant one
?

Patrice

 

[Kevin Yu [MSFT]]

Hi Jason,

I have tried it on my computer and got the same result as Patrice's. Since
Enterprise Manager will not refresh the Process list, could you please try
to disconnection and reconnect to the server in EM to refresh the list? If
that still doesn't work, could you please let me know the version of your
.NET framework?

Kevin Yu
=======
"This posting is provided "AS IS" with no warranties, and confers no
rights."

 

[Jason Collins]

..net 1.1.4322

The EM "staleness" isn't the culprit - I'm running unit tests that create
and destroy the db for each test fixture (NUnit). After the first test
fixture is done, the second test fixture times out because a connection
remains open to the first database, thus preventing it from being destroyed.
(Note that I've checked my code fairly extensively to ensure no connection
leaks - although it still may be a possibility.)

So, I guess there are 2 main questions:

1. With pooling=false, it is guaranteed that the underlying connection is
closed immediately when it is returned to the pool?
2. Does a connection pool maintain any sort of "administrative" connection
(e.g., heartbeat, etc.), even if pooling=false?

Thanks,
j

 

[Kevin Yu [MSFT]]

Hi Jason,

1) When pooling=false in the connection string, The unerlying connection
will be closed immediately when you call SqlConnection.Close(). It will not
be returned to the pool since pooling has been disabled.

2) A connection pool doesn't maintain any sort of administrative
connection. Please try to do a single experiment. Here are the steps:

1. Disconnect all the applications from the database.
2. Start Performance Monitor from Administrative Tools.
3. Add a new counter. Select SQLServer: General Statistics as Performance
object, and add User Connections.
4. Make sure that there is 0 connection to the server.
5. If there is some user connecting to the server, try to stop SQL server
service and start it again.
6. Create a single ADO.NET application with two buttons, one opens a
connection, the other closes it. Make sure to turn off connection pooling
in the connection string.
7. Run the application, and you will see user connections disconnected
immediately when the SqlConnection.Close() method is called.

HTH.

Kevin Yu
=======
"This posting is provided "AS IS" with no warranties, and confers no
rights."

 

[Jason Collins]

I have been investigating this more.

I didn't mention before (because I didn't think it was important) that we're
using SqlXml 3 to interop with our database (Sql Server 2000 sp3a).

After tracing this code, the source of the "rogue" connection seems to be
within the SqlXmlCommand class (or the SqlXml libraries at least).

Does anyone know if SqlXml keeps administrative connections, or otherwise
caches connections (depite the connection string saying Pooling=false)?

Thanks,
j

 

[Kevin Yu [MSFT]]

Hi Jason,

Thanks for your additional information!

Based on my research, SqlXmlCommand uses connection pooling by default as
ADO does. That might be the cause that there is always a connection remain
to the server.

Since SqlXml uses OleDb provider for SQL Server to connection, we cannot
use pooling=false to disable pooling. Instead, we can add "OLE DB
Services=-4;" in the connection string to disable connection pooling.
Please try it in your application to see if it works. HTH.

Kevin Yu
=======
"This posting is provided "AS IS" with no warranties, and confers no
rights."