Technical Article

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 
(
spidsmallint 
,dbidsmallint 
,ObjIdint 
,IndIdsmallint 
,Typenchar(4) 
,Resource    nchar(16) 
,Mode nvarchar(8) 
,Status   nchar(6) 
)

create table #tmpUsers
(
spid int,
eid int,
statusvarchar(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

You rated this post out of 5. Change rating

Share

Share

Rate

You rated this post out of 5. Change rating