user connection timeout

[naushi]

This is a sql 6.5 issue but the problem is common among all versions.

There are several users (about 15) doing sql queries over the web against the database. In sp_who these processes display the source server and the status is basically 'sleeping' since the queries are quick. Yesterday no one could get in because the max number of processes was reached and upon querying (sp_who) I found out many processes in 'spinloop' status - probably a resource deadlock. I had to reboot the server to get out.

Is there a way I could automatically kill these processes after they are not active anymore. I see in server configuration there is an option 'remote conn timeout'. if I change it to 2 minutes, will this kill the inactive logons for more than 2 minutes?

Or if there is another solution to this please let me know. these processes have started to pile up and we get a spinloop situation in many of these. Regards,
Naushi Hussain
Naushi.hussain@honeywell.com

 

[dbtech]

'remote conn timeout': Will not kill inactive process.

Use SQL Profile to monitor the open/close connections.

check this link, U need to register to access the script.

http://www.sqlservercentral.com/scr...ryNm=Maintenance and Management &CategoryID=1

dbtech