usp_KillUsers2

,

This script expands on the usp_KillUsers script posted by Brian Knight, by adding the text of the process that is being killed and will even kill users who are accessing the database from with in a process of another database. Does not contain the NET SEND option though.

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[usp_KillUsers]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[usp_KillUsers]
GO

SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO


CREATE  PROCEDURE usp_KillUsers @dbname varchar(50) as
SET NOCOUNT ON

DECLARE @strSQL varchar(255)
DECLARE @handle binary(20)

PRINT 'Killing Users'
PRINT '-----------------'

create table #tmpBuffer 
		(
				EventType 	nvarchar(30) 
			,	Parameters 	Int 
			,	EventInfo 	nvarchar(255) 
		)

create table #tmpLocks 
		(
				spid		smallint 
			,	dbid		smallint 
			,	ObjId		int 
			,	IndId		smallint 
			,	Type		nchar(4) 
			,	Resource    nchar(16) 
			,	Mode 		nvarchar(8) 
			,	Status   	nchar(6) 
		)

create table #tmpUsers
		(
				spid 		int,
				eid 		int,
				status		varchar(30),
				loginname 	varchar(50),
				hostname 	varchar(50),
				blk 		int,
				dbname 		varchar(50),
				cmd 		varchar(30)
		)

insert into #tmpLocks exec sp_lock

insert into #tmpUsers exec sp_who


DECLARE LoginCursor CURSOR
READ_ONLY
FOR  select distinct spid,db_name(dbid)
	    from #tmpLocks		
		where db_name(dbid) = @dbname   /*SELECT spid, dbname FROM #tmpUsers WHERE dbname = @dbname*/

DECLARE @spid 			varchar(10)
DECLARE @dbname2 		varchar(40)
DECLARE @procRunning 	varchar(255)
DECLARE @sqlSTMT  		nvarchar(2000)
DECLARE @loginname  	varchar(50)
DECLARE @status 		varchar(30)

OPEN LoginCursor

FETCH NEXT FROM LoginCursor INTO @spid, @dbname2
WHILE (@@fetch_status <> -1)
BEGIN
	IF (@@fetch_status <> -2)
	BEGIN

		SET @sqlSTMT = 'DBCC INPUTBUFFER (' + @spid + ')'

		insert #tmpBuffer exec sp_executesql @sqlSTMT				
		
		select @procRunning = EventInfo
		from #tmpBuffer 

		select @loginname = loginname,@status = status
		from #tmpUsers 
		where spid = @spid
		 

		PRINT '  KILLING: ' 
		PRINT '     SPID: ' + @spid
		PRINT '     USER: ' + @loginname 
		PRINT 'STATEMENT: ' + @procRunning 
		PRINT '   STATUS: ' + @status
		print '=========================================================================================================='

		SET @strSQL = 'KILL ' + @spid
		EXEC (@strSQL)
	END
	FETCH NEXT FROM LoginCursor INTO  @spid, @dbname2
END

CLOSE LoginCursor
DEALLOCATE LoginCursor

DROP table #tmpBuffer
DROP table #tmpUsers
DROP table #tmpLocks

Rate

Share

Share

Rate