Clearing the Connection Pool

[Brian Adams]

My application is a two-tier desktop application hitting SQL server
via Ado.Net using the System.Data.SqlClient namespace.

The administrative portion application needs to gain exclusive access
to our database in order to perform some maintanence operations on the
database (restoring one or two tables). This part of the application
is maintained separately from the rest of the system. During this
time, we do not want our user interface to have to shut down.

The problem we are having is that the SqlClient namespace keeps the
open connections in its connection pool -- I cannot find a way to
force the pool to disconnect all of its connections.

My options at this point are:

(1) Not use connection pooling; I would prefer to maintain the pool as
some of our code is shared with a web application.

(2) Use SQL server to kill the outstanding pooled connections. We are
not sure that our users are configured with sufficient security.

(3) Configure the lifetime of connections to some low value and force
the user to wait some amount of time for the connections to die

and (4) Find a way to force the pool to clear out all connections to
the database. We only ever attach using a single connection string, so
only one pool would need to be cleared (I'm also fine clearing
everything out 'brute force').

So my questions: Is there a way to accomplish (4) without resorting to
reflecting through the internals of System.Data.SqlClient? and is
there a more appropriate solution that I'm missing?

Thanks for any help!
-Brian

 

[Angel Saenz-Badillos[MS]]

Unfortunatelly for Rtm and Everett there is no way to clear connection
pools, setting the connection lifetime value will not help you either since
this is a feature that is only usefull for server clusters load balancing.

The other options would probably work, connections that are returned to the
pool will be recycled in random(4-8) minutes, so if you can wait that long
this may be a good solution. Terminating the client process would also work,
the pool will be automatically cleared when the process completes.

 

[William \(Bill\) Vaughn]

Angel is right, but I would also consider using SQLDMO to cycle the server.
This invalidates all of the connections in the pool and also permits you to
start the server in single-user mode.

--
____________________________________
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.
__________________________________

Unfortunatelly for Rtm and Everett there is no way to clear connection
pools, setting the connection lifetime value will not help you either since
this is a feature that is only usefull for server clusters load balancing.

The other options would probably work, connections that are returned to the
pool will be recycled in random(4-8) minutes, so if you can wait that long
this may be a good solution. Terminating the client process would also work,
the pool will be automatically cleared when the process completes.

--
Angel Saenz-Badillos [MS] Managed Providers
This posting is provided "AS IS", with no warranties, and confers no rights.
Please do not send email directly to this alias. This alias is for newsgroup
purposes only.

My application is a two-tier desktop application hitting SQL server
via Ado.Net using the System.Data.SqlClient namespace.

The administrative portion application needs to gain exclusive access
to our database in order to perform some maintanence operations on the
database (restoring one or two tables). This part of the application
is maintained separately from the rest of the system. During this
time, we do not want our user interface to have to shut down.

The problem we are having is that the SqlClient namespace keeps the
open connections in its connection pool -- I cannot find a way to
force the pool to disconnect all of its connections.

My options at this point are:

(1) Not use connection pooling; I would prefer to maintain the pool as
some of our code is shared with a web application.

(2) Use SQL server to kill the outstanding pooled connections. We are
not sure that our users are configured with sufficient security.

(3) Configure the lifetime of connections to some low value and force
the user to wait some amount of time for the connections to die

and (4) Find a way to force the pool to clear out all connections to
the database. We only ever attach using a single connection string, so
only one pool would need to be cleared (I'm also fine clearing
everything out 'brute force').

So my questions: Is there a way to accomplish (4) without resorting to
reflecting through the internals of System.Data.SqlClient? and is
there a more appropriate solution that I'm missing?

Thanks for any help!
-Brian