Kill orphaned connections

kill-orphaned-connections.sql

DECLARE @SQL AS VARCHAR(255)
DECLARE @SPID AS SMALLINT
DECLARE @Loginname AS VARCHAR(500)
DECLARE @HoursSinceLastBatch AS SMALLINT = 4
SET @Loginname = '<<Loginname>>'

DECLARE Murderer CURSOR FOR
SELECT spid FROM sys.sysprocesses WHERE loginame = @Loginname AND last_batch < DATEADD(HOUR, -1 * @HoursSinceLastBatch, GETDATE()) AND [status] = 'sleeping' ORDER BY last_batch DESC

OPEN Murderer

FETCH NEXT FROM Murderer INTO @SPID
WHILE @@FETCH_STATUS = 0

    BEGIN
    SET @SQL = 'Kill ' + CAST(@SPID AS VARCHAR(10)) + ';'
    EXEC (@SQL)
    PRINT  ' Process ' + CAST(@SPID AS VARCHAR(10)) +' has been killed'
    FETCH NEXT FROM Murderer INTO @SPID
    END 

CLOSE Murderer
DEALLOCATE Murderer