Friday, 24 July 2015

MSSQL - Kill all my processes (except current)

Template to kill all my processes (connections) except current

USE master
GO

DECLARE @sql VARCHAR(MAX) = '-- Kill all my processes (except current: '
                          + CAST(@@SPID AS VARCHAR(20)) + ')';
SELECT @sql = @sql + CHAR(13) + CHAR(10) + 'kill ' + CONVERT(varchar(5), spid) + ';'
FROM master..sysprocesses
WHERE hostname = HOST_NAME() AND spid != @@SPID;

PRINT @sql;
EXEC (@sql);

After execution we have something like this:

-- Kill all my processes (except current: 80)
kill 52;
kill 53;
kill 73;