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)
