This can be really useful if you have a run away workstation or application that is running on a specific client host which is hammering a MSSQL server. It will allow you to kick all the connections from that specific host very quickly.
Before running the stored procedure you may want to make sure which hosts have the most logins if you don't already know. By running the following which will give you the total number of logins per hostname.

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

From that you can then kill the connections using the following store procedure.

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




Last Modified: 12 December 2016

Releated Posts


2012-08-07 - MSSQL - Finding the database restore history
2011-09-24 - MSSQL - Checking Uptime
2011-08-11 - MSSQL - Saving memory by using stored procedures
2011-08-09 - MSSQL - Last Backup time and size
2011-06-24 - MSSQL - Extracting part of an email address
2011-06-15 - MSSQL - Bulk Deleting rows with backup
2011-06-07 - MSSQL - DBCC Check All Databases
2011-06-03 - MSSQL - Who locks what
2011-05-26 - MSSQL - RandomString Function
2011-05-22 - MSSQL - Removing the aspnet membership database
2011-05-19 - MSSQL - ShrinkLog
2011-05-16 - MSSQL - Enum
2011-05-05 - MSSQL - Log Sizes
2011-03-01 - MSSQL - Kill connections by host
2011-02-23 - C Sharp / MSSQL Get inserted value of NEWSEQUENTIALID()
2011-02-22 - MSSQL - Kill connections by username
2011-02-20 - MSSQL - All temp tables are global
2011-02-20 - MSSQL - Unsigned int
2011-02-17 - MSSQL - Convert IP To big int
2011-02-05 - MSSQL - Levenshtein
2011-02-02 - MSSQL - Kill connections to a database
2011-02-01 - MSSQL - Convert unix timestamp to date time
2011-01-30 - MSSQL - Enabling CLR Functions
2011-01-30 - MSSQL - RegEx Support
2011-01-30 - MSSQL - Adding MD5 checksum support
2011-01-30 - MSSQL - Adding SHASum support
2011-01-29 - MSSQL - TRIM Function