Kill SQL Server Sleeping Connections

Is this possible to do it from front-end i.e. ASP.NET C# Code ?

 

Is it possible to kill the connections from C# Code itself??

 

Please guide..

Hello Mam,

 

 

Use this Procedure to Kill the sleeping sessions U can call this from ado.net 

it will select 1 if all goes well 

 

remember the SPID<50 is the system process which cant be killed for that u have to kill the sessions for that

 

=================================================================

alter Procedure usp_KillSleepingsessions
as
begin

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

select 1 as success

end

==============================================

 

 

Reference for this 

https://forums.asp.net/t/1026195.aspx?How+can+I+kill+sleeping+processes+in+SQL+Server+ 

 

Hope this helps u 

Happy Coding :) 

Hi all..

 

I have written - con.Close(); con.Dispose() - in finally block ...

 

But, these session are not getting killed - They remain in sleeping mode for long duration of time ...

 

Hence...

Hi..

 

Thanks for this reference ..

 

But,  I need to kill all sleeping sessions from front-end - c# code..

 

How to do that ?

If I may ask, why exactly you plan to do this? I don't think this needs to be done as SQL Server is smart enough to manage it itself.

 

If you are facing any issue, observing some pattern then route cause needs to be investigated and fixed. Killing connections manually is not a solution.

 

For example, you may possibly have a piece of code which is not closing the connection after it's purpose is served. Then you should change the code and close the connection.