SQL Connections with VB.NET

[Joe Cool]

As most of you are aware, when you close a VB.NET connectrion to a SQL
server, the connection doesn't actually drop right then and there.
From my tests, using VB.NET 2003 and SQL2K, the connection doesn't
timeout and drop off for 6 1/2 minutes. How can one force this
connection to immediately drop off with code?

 

[Marina]

This is called connection pooling. It is a good thing, why would you want to
get rid of it?

 

[Daniel vom Saal]

I don't believe there is a way within .NET's CLR to clear the connection
pool in ADO.NET 1.x (which would close the actual SQL Server connection);
I've read that this should be available in 2.0, but I don't know if that is
the case.

Is there a reason you need to force the full SQL Server disconnect?

 

[Paul Clement]

¤ As most of you are aware, when you close a VB.NET connectrion to a SQL
¤ server, the connection doesn't actually drop right then and there.
¤ From my tests, using VB.NET 2003 and SQL2K, the connection doesn't
¤ timeout and drop off for 6 1/2 minutes. How can one force this
¤ connection to immediately drop off with code?

Not sure which provider or driver you are using with SQL Server but you could probably disable
connection pooling, although I wouldn't recommend it.


Paul
~~~~
Microsoft MVP (Visual Basic)

 

[Joe Cool]

This is called connection pooling. It is a good thing, why would you want to
get rid of it?

As you and others have asked "Why?" here's the answer. I am developing
a VB.NET application that uses SQL as the database backend and I check
the existance of all required tables when the application starts. If
any tables are missing, I assume the database is corrupt and give the
user the option of deleting the current corrupt database and
recreating it. I open a connection to scan the database for the
requied tables and iof the user wants to start over, I would like to
close the connection and immediately open a connection to the master
database and drop the application database. But with connection
pooling, it appears that I will have to wait for 6 1/2 minutes (in my
case) before I can do that. I would prefer to not have to wait.

Yes, connection pooling is a good thing, but I would like to
temporarily disable it in this case.

 

[Erland Sommarskog]

As you and others have asked "Why?" here's the answer. I am developing
a VB.NET application that uses SQL as the database backend and I check
the existance of all required tables when the application starts. If
any tables are missing, I assume the database is corrupt and give the
user the option of deleting the current corrupt database and
recreating it. I open a connection to scan the database for the
requied tables and iof the user wants to start over, I would like to
close the connection and immediately open a connection to the master
database and drop the application database. But with connection
pooling, it appears that I will have to wait for 6 1/2 minutes (in my
case) before I can do that. I would prefer to not have to wait.

No, you don't have to wait. If the credentials are the same you don't
even have to change connection, just issue "USE master". If you have
different credentials, or want a new connection anyway, issue a
"USE tempdb" on the old connection before you close it, so that the
"DROP DATABASE" command is not blocked. Yet another possibility is
to issue "ALTER DATABASE db SET SINGLE_USER WITH ROLLBACK IMMEDIATE"
before you drop the database. That will kick out the connections
that lingers around.

 

[MrDom]

We had to disable connectioning pooling on our ASP.Net 2002 and ASP.Net
framework 2003 applications becuase we got intermittent errors about
the connectionpool running out and there was no actual way to get rid
of it.

For the record we were closing and disposing of out connection objects
properly but despite this, we would get the dreaded erorr unexpectedly,
and have to reset the server to sort it out.

 

[Greg D. Moore \(Strider\)]

As you and others have asked "Why?" here's the answer. I am developing
a VB.NET application that uses SQL as the database backend and I check
the existance of all required tables when the application starts. If
any tables are missing, I assume the database is corrupt and give the
user the option of deleting the current corrupt database and
recreating it. I open a connection to scan the database for the
requied tables and iof the user wants to start over, I would like to
close the connection and immediately open a connection to the master
database and drop the application database. But with connection
pooling, it appears that I will have to wait for 6 1/2 minutes (in my
case) before I can do that. I would prefer to not have to wait.

Yes, connection pooling is a good thing, but I would like to
temporarily disable it in this case.

As Erland points out, you can do what you want w/o disabling connection
pooling.

But, my greater question is... while it may be "nice" you're checking for a
corrupt db, is this really worth it?

I mean SQL Server is fairly stable and if you're having this as a common
problem, you've got other issues to deal with.

Also, if the DB is corrupt, as a user, I'd probably want to RESTORE it from
a backup, not lose all my existing data.

I'll assume you have your reasons here, but I am curious.

 

[Joe Cool]

No, you don't have to wait. If the credentials are the same you don't
even have to change connection, just issue "USE master". If you have
different credentials, or want a new connection anyway, issue a
"USE tempdb" on the old connection before you close it, so that the
"DROP DATABASE" command is not blocked. Yet another possibility is
to issue "ALTER DATABASE db SET SINGLE_USER WITH ROLLBACK IMMEDIATE"
before you drop the database. That will kick out the connections
that lingers around.

Thanks. Very good info.

 

[Joe Cool]

As Erland points out, you can do what you want w/o disabling connection
pooling.

But, my greater question is... while it may be "nice" you're checking for a
corrupt db, is this really worth it?

I mean SQL Server is fairly stable and if you're having this as a common
problem, you've got other issues to deal with.

Also, if the DB is corrupt, as a user, I'd probably want to RESTORE it from
a backup, not lose all my existing data.

I'll assume you have your reasons here, but I am curious.

I just don't trust the user not go go mucking around with the
Enterprise Manager and accidentally screwing something up.

 

[Erland Sommarskog]

We had to disable connectioning pooling on our ASP.Net 2002 and ASP.Net
framework 2003 applications becuase we got intermittent errors about
the connectionpool running out and there was no actual way to get rid
of it.

For the record we were closing and disposing of out connection objects
properly but despite this, we would get the dreaded erorr unexpectedly,
and have to reset the server to sort it out.

Nevertheless, it's was a hole in your application, not in ADO .Net. If
memory serves, I think the common error is to not close the command
object before you close the connection.

Bill Vaugbn had some good articles on the ADO .Net connection pool
in the May and July 2003 issues of SQL Server Magazine. See
http://www.windowsitpro.com/Article/ArticleID/38356/38356.html and
http://www.windowsitpro.com/Article/ArticleID/39031/39031.html.
(The second article is for subscribers only, but the first is
publically available.)

 

[Greg D. Moore \(Strider\)]

I just don't trust the user not go go mucking around with the
Enterprise Manager and accidentally screwing something up.

Agreed, but that doesn't really answer any of my questions.

 

[Joe Cool]

Agreed, but that doesn't really answer any of my questions.

Well, it did any the one question you aasked but it didn't address
your recommendation of simply restoring from a backup rather than
losing all the current data, and I do agree with that. IF the user had
bothered to make a backup.