Kill SQL Server Sleeping Connections

Question

Dear all Do anyone know how to kill Kill SQL Server Sleeping Connections from asp.net c# Code ? I need to kill all sessions and connections in SQL Server that are in sleeping mode.. Please guide how to do that ?? From front-end .. c# Code.. Thanks in advance ..

Riddhi Valecha · Answer

Hi all.. 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..

Mangesh Gaherwar · Answer

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 ================================================================= 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(milast_batchGETDATE())>=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 :)

Riddhi Valecha · Answer

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...

Riddhi Valecha · Answer

Hi.. Thanks for this reference .. But I need to kill all sleeping sessions from front-end - c# code.. How to do that ?

Nitin Sontakke · Answer

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.

Mangesh Gaherwar · Answer

HI there You can refer this please http://googleweblight.com/?lite_url=http://www.stev.org/post/2011/03/01/MS-SQL-Kill-connections-by-host.aspx&lc=en-IN&s=1&m=163&host=www.google.co.in&ts=1482221716&sig=AF9Nedm1sFiXS64QcBuOi8RaKHim1ZcAOw In this author has created the stored procedure to do so So from.c# code you can call this procedure to achieve it