Monday, August 18, 2014

SQL Server Bulk Session kill script

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

In Order to run a trace against SQL Server you must be a member of sysadmin. (Enable trace access without granting sysadmin role)

Use below query to enable trace access without granting sysadmin role.


Use Master;
GRANT VIEW SERVER STATE TO [Domain\ADGroup or User];
GRANT SHOWPLAN TO [Domain\ADGroup or User];
GRANT ALTER TRACE TO [Domain\ADGroup or User];




Checkout Deal of the Day on Amazon.

SQL Query to get IP address of local & client machine



SELECT
   CONNECTIONPROPERTY('net_transport') AS net_transport,
   CONNECTIONPROPERTY('protocol_type') AS protocol_type,
   CONNECTIONPROPERTY('auth_scheme') AS auth_scheme,
   CONNECTIONPROPERTY('local_net_address') AS local_net_address,
   CONNECTIONPROPERTY('local_tcp_port') AS local_tcp_port,
   CONNECTIONPROPERTY('client_net_address') AS client_net_address;
 
© Copyright 2010-2012 Learn MySQL All Rights Reserved.
Template powered by Blogger.com.