How can I kill sleeping processes in SQL Server?

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 you

EXECsp_who

it will give you the SPID's for all your processes. Armed with that you could then issue  
KILL 42
which will terminate SPID 42
So 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.
 

HTH|||

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