DBA - Script for sp_chi_kill

create proc dbo.sp_chi_kill

(

@spid varchar(5) = null,

@dbname sysname = null,

@loginame varchar(50) = null,

@hostname varchar(50) = null

)

as

/*  Date: 10/25/2002

**  Author: Richard Ding

**  Purpose: To kill spids based on dbname, loginame or all.

**  URL: http://www.sqlservercentral.com/scripts/listscripts.asp?categorynm=Maintenance%20and%20

**              Management%20&categoryid=1

Hans Brink 2/1/2016

Took 14 years, but there is an update. 

Added in some additional identifiers for system processes, since those cannot be killed.

-everything spid 50 and below is a system processes

-added in the SQL Agent Job invocation engine thread.

**  Test code:

**    sp_chi_kill @dbname = 'choice'

**    sp_chi_kill @hostname = 'winweb2'

**    sp_chi_kill @loginame = 'chi_user' 

**    sp_chi_kill @spid = 34

**    sp_chi_kill 1964

*/

begin

set nocount on

SELECT spid, db_name(dbid) as 'dbname', loginame, hostname 

INTO #spids_to_kill 

FROM master.dbo.sysprocesses WITH (NOLOCK)

WHERE spid > 50 

AND status NOT IN ('background') 

AND cmd NOT IN ('SIGNAL HANDLER', 'LOCK MONITOR', 'LAZY WRITER', 'LOG WRITER', 'CHECKPOINT', 'CHECKPOINT SLEEP')

AND program_name NOT IN ('SQLAgent - Generic Refresher', 'SQLAgent - Email Logger', 'SQLAgent - Alert Engine','SQLAgent - Job invocation engine')

declare @total_logins int, 

        @csr_spid varchar(100)

if @spid is not null

  exec ('kill ' + @spid)

else 

  begin

    if @dbname is not null

      declare csr_spid cursor fast_forward for 

        select distinct spid from #spids_to_kill where dbname = @dbname and spid <> @@spid

    else if @loginame is not null

      declare csr_spid cursor fast_forward for 

        select distinct spid from #spids_to_kill where loginame = @loginame and spid <> @@spid

    else if @hostname is not null

      declare csr_spid cursor fast_forward for 

        select distinct spid from #spids_to_kill where hostname = @hostname and spid <> @@spid

    else if @spid is null and @dbname is null and @loginame is null and @hostname is null

      declare csr_spid cursor fast_forward for 

        select distinct spid from #spids_to_kill where spid <> @@spid

    begin

      open csr_spid

      fetch next from csr_spid into @csr_spid

      while @@fetch_status = 0

        begin

          set nocount on     

          exec (' kill ' + @csr_spid )

          if @@error = 0

            print 'spid ' + @csr_spid + ' was killed.'

          fetch next from csr_spid into @csr_spid

        end

      close csr_spid

      deallocate csr_spid

    end

  end

end