Kill Sessions by HOST SQL Server

–Check no. of sessions coming from all hosts connected to DB —
SELECT hostname, COUNT(hostname) FROM sys.sysprocesses P
JOIN sys.sysdatabases D ON (D.dbid = P.dbid)
JOIN sys.sysusers U ON (P.uid = U.uid)
WHERE hostname != ”
GROUP BY hostname
ORDER BY COUNT(hostname) DESC;

— Create Procedure to kill sessions by Hostname —
CREATE PROCEDURE [dbo].[KillConnectionsHost] @hostname varchar(MAX)
AS
DECLARE @spid int
DECLARE @sql varchar(MAX)

DECLARE cur CURSOR FOR
SELECT spid FROM sys.sysprocesses P
JOIN sys.sysdatabases D ON (D.dbid = P.dbid)
JOIN sys.sysusers U ON (P.uid = U.uid)
WHERE hostname = @hostname AND hostname != ”
AND P.spid != @@SPID

OPEN cur

FETCH NEXT FROM cur
INTO @spid

WHILE @@FETCH_STATUS = 0
BEGIN
PRINT CONVERT(varchar, @spid)

SET @sql = ‘KILL ‘ + RTRIM(@spid)
PRINT @sql
EXEC(@sql)

FETCH NEXT FROM cur
INTO @spid
END

CLOSE cur
DEALLOCATE cur
— Execute Procedure by passing Hostname as parameter in the Stored Procedure —

exec KillConnectionsHost MSSCOM01