AWAITING COMMAND idle connections. 1

[uncLez]

Hi all,

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.

 

[rt63]

Sometimes setting the connection object to null solves the problem (VBScript)

set <connobj variable> = null

RT

 

[uncLez]

I forgot to mention, I always use:

conn.Close
SET conn = NOTHING

when I close the object, so I more or less already do as you propose... Should I use NULL or NOTHING here? I mean, is there a chance that this would lead to a different result?

 

[rt63]

Sorry, I meant NOTHING only.

We also faced a similar problem. We found a solution which is not very elegant though. We have created a recurring job to kill idle connections. For the specific web-based app. we have assigned a specifc login id and in the job we scan for processes owned by these logins and which have been idle for some time and kill them.

I am also quite keen to know a better solution for this.


RT

 

[uncLez]

Ok,
thanx for your help so far!