CREATE procedure sp_murder @name varchar(128)=null, @doit bit=0 AS
set nocount on
declare @sql varchar(8000)
set @sql=''
IF @name IS NOT NULL
select @sql=@sql + 'KILL ' + cast(spid as varchar) + '; ' +char(13) + char(10) from sysprocesses
where loginame LIKE '%' + @name + '%' or db_name(dbid) LIKE '%' + @name + '%'
ELSE
select @sql=@sql + 'KILL ' + cast(spid as varchar) + '; ' +char(13) + char(10) from sysprocesses
where (status='sleeping' and spid>8 AND dbid>1 AND nt_username<>'SYSTEM' and login_time<getdate()-1 and last_batch<getdate()-.25)
if @doit=0 select @sql ELSE exec(@sql)
Usage:
exec sp_murder --lists spids that are killable cause they're old
exec sp_murder null, 1 --actually kill the spids returned from above
exec sp_murder 'myDB', 1 --kill users in myDB
exec sp_murder 'username', 1 --kill all connections made by user "username"
Kill multiple user connections
Posted - 09/17/2004 : 08:18:09
