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