How to Kill Processes Idle for X Hours? – Interview Question of the Week #152

Question: How to Kill Processes Idle for X Hours?

Answer: When I heard this question first time, my reaction to this question was why would you ever need to do this for? The answer, I received was that user had so many open connection due to bad .net code that they were continuously running out of the thread for their SQL Server. The issue was a classic case of connection leaking by .net code not closing the connections which were not open.

How to Kill Processes Idle for X Hours? - Interview Question of the Week #152 killprocess-800x257

The best solution was to fix the .net code which was opening connection and not closing it. While the developer team identifies the culprit, the DBA can run following code to close any connection which were open, many hours ago and not doing anything for the last 8 hours.

The following code when executed will kill any connection which has not been doing anything for 8 hours. You can change this code to run for 24 hours or even 100 hours. In any case, please note that you are closing the connection, which are not doing anything. The only downside of this code is that if you have open any transaction 8 hours ago and the thread not doing anything, it will be rolled back.

SET NOCOUNT ON
DECLARE @spid SMALLINT, @spidString VARCHAR(12)
--Declaring Cursor
DECLARE spidCursor CURSOR
    FOR SELECT spid
        FROM master.sys.sysprocesses
        WHERE last_batch < DATEADD(hh, -8, GETDATE()) AND spid > 50 -- Change 8 to any other value for hours AND spid > 50 -- for user spid
    FOR READ ONLY
OPEN spidCursor
FETCH NEXT FROM spidCursor INTO @spid
-- Processing kill logic
SELECT
    'Killed spid(s) - '
WHILE (@@fetch_status = 0)
    AND (@@error = 0)
    BEGIN
        SELECT @spidString = CONVERT(VARCHAR(12), @spid)
        EXEC ('kill ' + @spidString)
        SELECT @spid
        FETCH NEXT FROM spidCursor INTO @spid
    END
-- Closing cursor
CLOSE spidCursor
DEALLOCATE spidCursor
SET NOCOUNT OFF

Let me know if you have better code, I will be interested to know a better way to do this task.
Reference: Pinal Dave (https://blog.sqlauthority.com)

, , , ,
Previous Post
How to Identify Session Used by SQL Server Management Studio? – Interview Question of the Week #151
Next Post
How to List All ColumnStore Indexes with Table Name in SQL Server? – Interview Question of the Week #153

Related Posts

Leave a Reply

Menu