SQL Server Performance Forum – Threads Archive
Sleeping connections
Hello, we are running an old online application, which at the end of the day has some 300-400 sleeping connections. We do not know what causes them and tried a bunch of things to get ride of them and only killing them works. We run the kill process at night and start the day afresh only to get another few hundred at the end of the next dat. We have ASP pages talking to COM+ components written Visual J++, that call stored procs on SQL Server 2000 Ent Ed. server. Here’s the output of SP_WHO2 for the dead connections (notice that the OS is reported as the last program, we also see IIS in that spot):66 sleeping userDN530WSB database AWAITING COMMAND0 0 10/18 12:08:07Microsoft(R) Windows (R) 2000 Operating System66
67 sleeping userDN530WSB database AWAITING COMMAND0 0 10/18 12:08:07Microsoft(R) Windows (R) 2000 Operating System67
68 sleeping userDN530WSB database AWAITING COMMAND0 0 10/18 12:08:07Microsoft(R) Windows (R) 2000 Operating System68
70 sleeping userDN530WSB database AWAITING COMMAND0 0 10/18 12:08:07Microsoft(R) Windows (R) 2000 Operating System70
77 sleeping userDN530WSB database AWAITING COMMAND0 0 10/18 12:08:08Microsoft(R) Windows (R) 2000 Operating System77
78 sleeping userDN530WSB database AWAITING COMMAND0 0 10/18 12:08:08Microsoft(R) Windows (R) 2000 Operating System78
80 sleeping userDN530WSB database AWAITING COMMAND16 0 10/18 12:08:08Microsoft(R) Windows (R) 2000 Operating System80
81 sleeping user DN530WSB database AWAITING COMMAND0 0 10/18 12:08:08Microsoft(R) Windows (R) 2000 Operating System81 Any hints or solutions would be greatly appreciated. Radek
Usually Awaiting Command in sp_who, means user don’t close connection.
Something like: User go home and live application open.
Luis Martin
Moderator
SQL-Server-Performance.com All postings are provided “AS IS†with no warranties for accuracy.
If those idle connections are not causing any issues on the performance then you can ignore and next day users can continue their work as usual, if any issues then here are URLs of two scripts that you might find useful if you need to kill multiple user connections in one fell swoop. http://www.sqlservercentral.com/scr…ryNm=Maintenance and Management &CategoryID=1 http://www.admin911.com/downloads/usp_KillUsers.sql You can use these scripts as is, or modify them to meet your own specific needs. Once you have come up with a script you like, you can convert it to a stored procedure, and then run the stored procedure as part of the job you use to restore data on the backup server. http://www.sql-server-performance.com/q&a37.asp Satya SKJ
Moderator
http://www.SQL-Server-Performance.Com/forum
This posting is provided “AS IS†with no rights for the sake of knowledge sharing.
Make sure that the COM+ objects close the ADO connection/resultset/command and set the object to Nothing. This sounds like a problem with one or more COM components Cheers
Twan
]]>