Ask for SQL server instance, login and hostname for which the sessions need to be killed and replace the value of @login and @host in below script and execute it on the servers where the session need to be killed.
declare @login varchar (200)
declare @host varchar (200)
declare @id int
declare @host_desc varchar(200)
declare @login_desc varchar (200)
declare @str nvarchar(200)
/* Replace login and hostname value*/
SET @login = '<login-need-to-be-killed>'
SET @host = '<login-from-which-host>'
DECLARE cur_kill CURSOR
STATIC FOR
SELECT spid SPID,
convert(sysname,rtrim(loginame)) Login,
convert(varchar(50),rtrim(hostname)) Host
FROM
master.dbo.sysprocesses a (nolock)
inner join master.dbo.sysdatabases b (nolock) on a.dbid=b.dbid
WHERE
spid > 50 and loginame = @login and hostname = @host
ORDER BY
cpu desc
OPEN cur_kill
IF @@CURSOR_ROWS > 0
BEGIN
FETCH NEXT FROM cur_kill INTO @Id, @login_desc, @host_desc
WHILE @@Fetch_status = 0
BEGIN
set @str = 'kill ' + cast(@id as nvarchar(max))
--print @str
exec (@str)
PRINT '[KILLED] - ID : '+ convert(varchar(20), @Id ) + ', Login : '+@login_desc+ ', Host : '+convert(varchar(20),@host_desc)
FETCH NEXT FROM cur_kill INTO @Id, @login_desc, @host_desc
END
END
CLOSE cur_kill
DEALLOCATE cur_kill
Posts
