SQL Server Bulk Session kill script

Monday, August 18, 2014

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
Related Posts Plugin for WordPress, Blogger...

1 comments:

Post a Comment

 
© Copyright 2010-2012 Learn MySQL All Rights Reserved.
Template powered by Blogger.com.