Help! What's with the open connection?

  • Thread starter Thread starter Wardeaux
  • Start date Start date
W

Wardeaux

Hey, all!

I have an asp.net page, uses ADO.NET connection to create a DB and insert a
UID/Password into one of the tables inside the new DB. Here's what I'm
seeing:

1) If I ONLY create the DB("Create database abc"), I can delete the DB right
away.
2) If I create the DB and simply OPEN a connection to the new DB, I can't
delete the DB for 5 minutes: says it is currently in use.

again, if I only run func1, everything deletes fine, if I run Func1 then
func2, a connection is being left open...

here's my code:

function func1()

strSQL = "Create database ABC"

oDBCon = New SqlClient.SqlConnection(MyFirstConnString)

oDBCon.Open()

Dim oDataCMD As New SqlClient.SqlCommand(strSQL, oDBCon)

RowsAffected = oDataCMD.ExecuteNonQuery()

oDataCMD.Dispose()

end function



function func2()

oDBCon2 = New SqlClient.SqlConnection(MyNewConnString)

oDBCon2.Open()

oDBCon2.Close()

end function



Is this a timing issue or something like SQL has to update a something and
it takes 5 mins before it is available? ANY help is appreciated!!

wardeaux
 
Wardeaux,

Use the Close or Dispose method of the connection object (oDBCon) to release
it back into the connection pool. Look up the SqlConnection.Close method in
your MSDN help library.

Raymond Lewallen
 
Raymond,
Thanks for the reponse... unfortunately I already do call oDBCon.close
(forgot to include that in the code for Func1). But Func1 is not the issue
since I can run Func1 by itself and then delete the DB(so ALL connections
are closed after calling Func1). It's only when I run Func2 that I get the
error. I've also tried Dispose instead of close and in combination with
close... no difference... any other suggestions? I could really use one!!
:)
thanks again!
wardeaux
 
Yes, but unless you disable the pooler, the connection will be held for 4-8
minutes (as he was seeing).

--
____________________________________
William (Bill) Vaughn
Author, Mentor, Consultant
Microsoft MVP
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.
__________________________________
 
Ah, I wasn't thinking. He's trying to delete an entire database, in which
case my suggestion would not work with pooling enabled. Deleting objects in
the database would be fine at this point, but the pool still has the
connection to the database. Wardeax, you might trying setting pooling=false
or the lifetime to nSeconds in your connection string to help you out there.
Might look up some info on connection pooling in the help.

Sorry for the initial misinformation, and thanks to William for his
correction to my post.

Raymond Lewallen
 
Guys,
Thanks so much!! As you can tell I'm fairly new to the "inner bowels" of
SQL Server... I understand that "pooling" is a good thing for performance.
Here's my anxiety: I'm going to be having somewhere between 500 and 2000
simultaneous asp.net sessions using about 500 different SQL DBs... My
concern is this: what is going to happen if everytime I open a connection to
a DB, and I tell it to "Close" but it gets left open in a pool for 4-8
mins... will I be getting connection denials (too many open connections) or
is SQL smart enough to deal with this... or am I just getting paranoid with
my first solo commercial release?
Any help/reply is most appreciated!!!
thanks again!
wardeaux
 
If the database is different then the ConnectionString is going to include a
different "Initial Catalog=" value so you'll get a different connection
pool. I don't know why you have so many databases... that would really hurt
performance for a number of reasons. The SS engine has an upper limit of
connections but it's very high, but each connection takes memory (40-50k) so
it's important to optimize the number of connections you're creating.

--
____________________________________
William (Bill) Vaughn
Author, Mentor, Consultant
Microsoft MVP
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.
__________________________________
 
Bill,
thanks again for the reply. Unfortunately the high number of DBs is a
result of every client gets their own DB (each client could have 4 or more
logins representing different employees) , that's why I was saying 2000
simultaneous connections. Is there a practical limit where I should begin
to look at additional SQL servers? Where's the place to look for the number
of connections my SQL will allow?
MTIA
wardeaux
 
I would ask this question over in the SQL Server newsgroup. Several of the
SS MVPs hang out there and they can answer this better than I. Yes, I would
expect that more servers might be called for... but there are a lot of
variables that really dictate when this makes sense.

--
____________________________________
William (Bill) Vaughn
Author, Mentor, Consultant
Microsoft MVP
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.
__________________________________
 
Back
Top