SQL error... (idle connections)

[uncLez]

Hi all,

I posted this on the SQL Server forum as well, hope you guys don't mind, but this seemed a logical place to also post my question...

I am having this (imho weird) problem on MS SQL Server 6.5 (yeah, I know, we need to upgrade... BAD ) which is as follows...

I am running Queries against various databases from ASP scripts. I close my connection right away whenever I don't need it anymore. (Using the "Connection.Close" statement)

On the SQL Server however they remain open as idle connections displaying "AWAITING COMMAND" as the Command detail, which sometimes causes the server to exceed the maximum number of connected users, thus creating errors for every site for which it hosts the database.

How can I prevent this (besides using a lower "remote conn timeout" value)?

Thanx in advance,
Anton de Wit.

 

[Tarwn]

Are you also making sure to set your objects = Nothing? There was a bug at one point in time that would cause memory problems where the memory wasn't being released even after the objects were closed, they actually had to be set to nothing in order for them to truly let go of the memory. If in this case the memory is a connection stream to your DB it's possible that the db doesn't realize the connection is closed (maybe).
This is a long shot, but it might work
-Tarwn "Customer Support is an art not a service" - marketing saying
"So are most other forms of torture" - programmers response
(The Wiz Biz - Rick Cook)

 

[uncLez]

Yeah, sorry,
I forgot to include that.

I always close connections like this:

connection.close
SET connection = NOTHING

So that should not be the problem... It is weird, cause the connection object is really gone, I tested that with some scripts, but the database keeps the connection wide open for some obscure reason...

 

[Lammers]

More than likely due to connection pooling.
Which is why the connection is still visable on the SQL Server.