Kill Inactive Session using Cursor

 I had a requirement to select all the inactive session with more than 24 hours and kill the process where status is "Sleeping".

Below is the small Cursor which will select all inactive session where status is "sleeping" and kill the process based on the spid.



DECLARE @SPID VARCHAR(20)
DECLARE @STATUS VARCHAR(20)
DECLARE @QUERY VARCHAR(20)
DECLARE CUR CURSOR FOR
SELECT spid FROM sys.sysprocesses WHERE db_name(dbid)='Ashish' and STATUS='sleeping' and login_time<GETDATE()-1
OPEN CUR
FETCH NEXT FROM CUR INTO @SPID
WHILE @@FETCH_STATUS = 0
BEGIN
SET @QUERY= 'KILL ' + @SPID
print (@QUERY)
EXEC (@QUERY)
FETCH NEXT FROM CUR INTO @SPID
END
CLOSE CUR
DEALLOCATE CUR