Killed Connection shows up in EM

[sqlturbo]

I connect to a SQL Server database thru a .net web app.
Now, if I go to Current Activity in EM I can see the details of the connection. However, EM show details of the process/connection even after I kill the application that connects to the database. It shows the status as "SLEEPING" and Wait Type as "NOT WAITING.
It shows the details for a couple of minutes before it gets rid of it. On the other hand if the connection is directly thru Query Analyser and is killed, the details of that connection disappears immediately from EM.

My concern is if I have thousands of hits on the website and users connect and a few minutes later disconnect, are their "killed" coonnection(s) eating up resources on the database server?

 

[JayKusch]

Yes they are eating up some resources. We sometimes refer to these types of connections as "ghost connections". No matter what do to kill the process, either thru Enterprise Manager or the KILL spid command in Query Analyzer, these connections may still remain.

You may have no other alternative but to stop and restart the SQL Server Service.

Thanks

J. Kusch

 

[sqlturbo]

Jay

Stopping the service is a bit drastic. I cannot do that on the prodiction server.

What is odd is that SQL Server knows the connection has been termintated and after about 3 minutes the process details no longer show up under Current Activity>Process Info. If it were a ghost connection, it won't know what to do but in this case it sort of does and after a while stops waiting for a command from the client and gets rid of the process altogether.

I found this on BOL:

One common cause of orphaned sessions arises when a client computer loses power unexpectedly, or is powered off without performing a proper shutdown. Orphaned sessions can also occur due to a hung application that never completely terminates, resulting in a dead connection. Windows NT does not know that the connection is dead and continues to report the action as active to SQL Server. SQL Server, in turn, keeps the session open and continues to wait for a command from the client.

 

[JayKusch]

I still beleive you will need to restart the SQL Server service to clear them. Please let us know if you find an alternative method.

Thanks

J. Kusch