sp_kill

,

This stored proc has helped me a lot when I needed to restore db, reconfigure replication, and do other database jobs. SQL server KILL command can only act on a single spid. This sp compiles KILL commands in a cursor to kill a group of spids based on database name, login name, host machine name or simply kills all except 'sa' when there are no parameters specified. It has been tested on SQL 7 and 2000.

Disclaimer: Use this stored proc at your own risk as killing processes may be undesirable under certain circumstances. Use it with caution.

use master
go

if exists (select name from sysobjects where name = 'sp_kill' and type = 'p' )
    drop proc sp_kill
go

create proc sp_kill 
		@dbname varchar(100) = null, 		--	When specified, kills all spids inside of the database
		@loginame varchar(50) = null, 	--	When specified, kills all spids under the login name
		@hostname varchar(50) = null		--	When specified, kills all spids originating from the host machine
as
begin
	set nocount on
	select spid, db_name(dbid) as 'db_name', loginame, hostname into #tb1_sysprocesses from master.dbo.sysprocesses (nolock)
	declare @total_logins int, @csr_spid varchar(100)		
	set @total_logins = ( select count(distinct spid) from #tb1_sysprocesses )
	if @dbname is null
	begin
		if @loginame is null
		begin
			if @hostname is null
			begin
				if @total_logins > 0
				begin 
				declare csr_spid cursor fast_forward for select distinct spid from #tb1_sysprocesses where loginame <> 'sa' and spid <> @@spid
				open csr_spid	
				fetch next from csr_spid into @csr_spid
				while @@fetch_status = 0
					begin
						set nocount on     
						exec ('kill ' + @csr_spid)
						fetch next from csr_spid into @csr_spid
					end
				close csr_spid
				deallocate csr_spid
				end
			end
			else
			begin
				if @total_logins > 0
				begin 
				declare csr_spid cursor fast_forward for select distinct spid from #tb1_sysprocesses where hostname = @hostname  and loginame <> 'sa' and spid <> @@spid
				open csr_spid	
				fetch next from csr_spid into @csr_spid
				while @@fetch_status = 0
					begin
						set nocount on     
						exec ('kill ' + @csr_spid)
						fetch next from csr_spid into @csr_spid
					end
				close csr_spid
				deallocate csr_spid
				end
			end
--------------------------------------------------
		end				
		else
		begin
				if @total_logins > 0
				begin 
				declare csr_spid cursor fast_forward for select distinct spid from #tb1_sysprocesses where loginame = @loginame and  loginame <> 'sa' and spid <> @@spid
				open csr_spid	
				fetch next from csr_spid into @csr_spid
				while @@fetch_status = 0
					begin
						set nocount on     
						exec ('kill ' + @csr_spid)
						fetch next from csr_spid into @csr_spid
					end
				close csr_spid
				deallocate csr_spid
				end
			
		end
-----------------------
	end
	else
	begin
				if @total_logins > 0
				begin 
				declare csr_spid cursor fast_forward for select distinct spid from #tb1_sysprocesses where db_name = @dbname  and loginame <> 'sa' and spid <> @@spid
				open csr_spid	
				fetch next from csr_spid into @csr_spid
				while @@fetch_status = 0
					begin
						set nocount on     
						exec ('kill ' + @csr_spid)
						fetch next from csr_spid into @csr_spid
					end
				close csr_spid
				deallocate csr_spid
				end

	end
	drop table #tb1_sysprocesses
end

Rate

Share

Share

Rate