MSSQL Kill Connection for a specific host
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 host name.
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
Did You find this page useful?
Thanks for the feeback. Please consider sharing with others.