Dear,
Our ASP.NET scripts send SQL statements (as inline SQL or SP) to process the requested job. After the job execution, the process ID stays in the server and waits for next command with sleeping status.Since this process does not go away, next job adds another process and eventually, the server is overloaded with these processes and dies.
How can I kill this sleeping processes?
Regards,
Echo
Off the top of my head if youEXECsp_who
it will give you the SPID's for all your processes. Armed with that you could then issueHTH|||KILL 42which will terminate SPID 42So I would guess that you could set up a job in SQL Server to get a list of all offending SPID's, schedule it to run every hour for arguments sake, and then step through that list issuing the KILL command.
Please do take care when using KILL to kill SQL sessions.SPID<=50 are SQL system processes, so you'd better not kill such processes. Always confirm that the session is no longer useful (has been idle for a long time or invovled in blocking/deadlock) before you KILL it. For more information about the KILL command in T-SQL, you can refer to:
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/tsqlref/ts_kf-kz_1zos.asp
|||
Iori_Jay:
Please do take care when using KILL to kill SQL sessions
I very much agree with Iori_Jay, and really should have advised caution in my earlier post.
|||For example, you can use such query to Kill SQL sessions which are sleeping and have been idle more than 1 hour:
DECLARE @.v_spid INT
DECLARE c_Users CURSOR
FAST_FORWARD FOR
SELECT SPID
FROM master..sysprocesses (NOLOCK)
WHERE spid>50
AND status='sleeping'
AND DATEDIFF(mi,last_batch,GETDATE())>=60
AND spid<>@.@.spid
OPEN c_Users
FETCH NEXT FROM c_Users INTO @.v_spid
WHILE (@.@.FETCH_STATUS=0)
BEGIN
PRINT 'KILLing '+CONVERT(VARCHAR,@.v_spid)+'...'
EXEC('KILL'+@.v_spid)
FETCH NEXT FROM c_Users INTO @.v_spid
END
CLOSE c_Users
DEALLOCATE c_Users
Dear all,
Thank U everybody for replying. But I do not want to run any process at SQL server. Can't I manage it from ADO.NET. Is it possible that after executing a process it will automatically die.
I think the problem may related to connection pooling. Can I stop connection pooling of ADO.NET programatically?
Regards,
Sultan
Why do you want to stop connection pooling...Connection Pooling can improve performance for database connections. If you just want to prevent unexpected remaining connecitons, you can just set the connection timeout when you create the connection in your code.
You can also take a look at this article:
Tuning Up ADO.NET Connection Pooling in ASP.NET Applications
