How to close all sleeping processes
-
Hi,
im working on a .net web application and i've got a problem related with sql database connections.
while im working on my web application the sql server generates processes for my database connections, the problem is that they stay with a sleeping status and dont disappear.
Is there a way to clear all sleeping processes?
Do that sleeping processes degrade the server performance?
Thanks in advance for any help.
RR
-
I've noticed this before when working in Visual Studio.NET (at least in versions other than 2003) I think it has to do with garbage collection not being run while the IDE is open. I don't know if 2003 fixes this. I suspect not.
These sleeping processes should not degrade performance. In any case, VS.Net is only likely to be used on a development server where performance isn't usually that critical. If you rellay want to clear these process you could create a cursor to loop through sysprocesses where status = 'sleeping' and kill each process. You will however notice some oddities in the IDE as connections get broken. I suspect you don't want to do this. If you shut down the IDE and these connections are still there you may want to kill them at that point, but you may also just verify if they will go away on there own as the VS.NET garbage collector runs. Check out http://qa.sqlservercentral.com/scripts/contributions/838.asp and modify to kill only processes where status = 'sleeping' . Make sure you don't kill processes from other developers who may not appreciate this.
Francis
-
I would also only kill those processes for certain logins as well. It would not be good to kill a login that is sleeping but is ultimately responsible for your replication.
-
Thanks for your help.
Im not using SQL Server from Visual Studio.NET IDE. Im just checking the active processes in Enterprise Manager.
The main problem is that sleeping processes should be killed automaticaly 60 secs after, but they are not.
Sometimes they're killed 2 or 3 minutes after being in sleeping status, and sometimes they're not killed.
-
Is your web application killing the connection once the page is done, or are you using connection pooling?
Connection pooling will leave the conneciton open so that another page can use it, this saves resources as you don't need to constantly create and destroy the connections.
-
If this is a .NET application is the connection object being closed and set to NOTHING? If so you'll just have to wait the 2-3 minutes for the connection to go away. I don't think its a big deal unless your SQL Server is short on memory. I believe each connection uses 1024 K of memory
Francis
-
Im using connection pooling.
My problem is that I have to support also MSDE and Im afraid that the sleeping processes degrade its performance.
-
How is your memory usage on the server? If sql is not taking it all and you have plenty of free memory within the chunk that SQL does have I would not be concerned about it.
If it's a huge concern remove connection pooling, however I think that doing so you will find that you are worse off.
-
We had the same problem, only our users were starting to get timeout errors. These connections are actually orphaned connections. I've tried killing these in the past, but did not have any success, so we simply stopped the server and restarted it. However, not really a good solution in a production environment!!!
You must remember to explicitly close and dispose the connections in .net.
I would be interested in a better solution than restarting Sql. Not having a DBA, we really don't know why the kill did not work.
-
At this time i'll leave it like it is.
Depending on the system that my application is installed sometimes i get some timeouts on the clients.
I'll keep searching for a solution and when i'll find it i'll post it here.
-
I got the same problem.The Server eat 2G memory on my machine after about two days work. Really hope someone could find a better solution then restart the server.
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply