Kill all SPIDs with a certain user ID
This script is used to disconnect all connections by a particular individual to a server. I use it when I have to disconnect somebody and they have too many processes to do it by hand. Normally, you can just connect the offending process, but if there are multiple processes that are causing a problem , you can use this script to just disconnect the user from the server.
If you run this procedure without any parameters, it will return a list of all active connections, along with the number of databases they're using and the number of connections they have. If you run it with a loginname as a parameter, it will kill all the instances of that login name.
WARNING - ANY UNCOMMITTED TRANSACTIONS WILL BE ROLLED BACK, SO USE CAREFULLY!
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO
/* AUTHOR: RMcCauley (with some help from Microsoft)
CREATED: 10/17/2003
PURPOSE: This procedure is user to kill multiple user processes
at once. If run without a parameter, it will list each
user currently connected, as well as the total number
of processes and databases their connected to. Run it
again with that person's login as a parameter, and the
procedure will shred each of that person's processes,
releasing their QA session to execute more poorly
planned and horribly un-optimized queries.
SIDE NOTE: Don't kill the 'sa' user - that's bad.
*/
ALTER PROCEDURE sp_killuser -- 1995/11/03 10:16
@loginame sysname = NULL
as
IF @loginame like 'sa'
BEGIN
Print 'You can''t kill the SA user!'
GOTO LABEL_86RETURN
END
set nocount on
declare
@retcode int
declare
@sidlow varbinary(85)
,@sidhigh varbinary(85)
,@sid1 varbinary(85)
,@spidlow int
,@spidhigh int
declare
@charMaxLenLoginName varchar(6)
,@charMaxLenDBName varchar(6)
,@charMaxLenCPUTime varchar(10)
,@charMaxLenDiskIO varchar(10)
,@charMaxLenHostName varchar(10)
,@charMaxLenProgramName varchar(10)
,@charMaxLenLastBatch varchar(10)
,@charMaxLenCommand varchar(10)
declare
@charsidlow varchar(85)
,@charsidhigh varchar(85)
,@charspidlow varchar(11)
,@charspidhigh varchar(11)
DECLARE @current_process int
--------
select
@retcode = 0 -- 0=good ,1=bad.
--------defaults
select @sidlow = convert(varbinary(85), (replicate(char(0), 85)))
select @sidhigh = convert(varbinary(85), (replicate(char(1), 85)))
select
@spidlow = 0
,@spidhigh = 32767
--------------------------------------------------------------
IF (@loginame IS NULL) --Simple default to all LoginNames.
GOTO LABEL_17PARM1EDITED
--------
-- select @sid1 = suser_sid(@loginame)
select @sid1 = null
if exists(select * from master.dbo.syslogins where loginname = @loginame)
select @sid1 = sid from master.dbo.syslogins where loginname = @loginame
IF (@sid1 IS NOT NULL) --Parm is a recognized login name.
begin
select @sidlow = suser_sid(@loginame)
,@sidhigh = suser_sid(@loginame)
GOTO LABEL_17PARM1EDITED
end
--------
IF (lower(@loginame) IN ('active')) --Special action, not sleeping.
begin
select @loginame = lower(@loginame)
GOTO LABEL_17PARM1EDITED
end
--------
IF (patindex ('%[^0-9]%' , isnull(@loginame,'z')) = 0) --Is a number.
begin
select
@spidlow = convert(int, @loginame)
,@spidhigh = convert(int, @loginame)
GOTO LABEL_17PARM1EDITED
end
--------
/*RaisError(15007,-1,-1,@loginame)
select @retcode = 1
GOTO LABEL_86RETURN
*/
LABEL_17PARM1EDITED:
-------------------- Capture consistent sysprocesses. -------------------
SELECT
spid
,status
,sid
,hostname
,program_name
,cmd
,cpu
,physical_io
,blocked
,dbid
,convert(sysname, rtrim(loginame))
as loginname
,spid as 'spid_sort'
, substring( convert(varchar,last_batch,111) ,6 ,5 ) + ' '
+ substring( convert(varchar,last_batch,113) ,13 ,8 )
as 'last_batch_char'
INTO #tb1_sysprocesses
from master.dbo.sysprocesses (nolock)
--------Screen out any rows?
IF (@loginame IN ('active'))
DELETE #tb1_sysprocesses
where lower(status) = 'sleeping'
and upper(cmd) IN (
'AWAITING COMMAND'
,'MIRROR HANDLER'
,'LAZY WRITER'
,'CHECKPOINT SLEEP'
,'RA MANAGER'
)
and blocked = 0
--------Prepare to dynamically optimize column widths.
Select
@charsidlow = convert(varchar(85),@sidlow)
,@charsidhigh = convert(varchar(85),@sidhigh)
,@charspidlow = convert(varchar,@spidlow)
,@charspidhigh = convert(varchar,@spidhigh)
SELECT
@charMaxLenLoginName =
convert( varchar
,isnull( max( datalength(loginname)) ,5)
)
,@charMaxLenDBName =
convert( varchar
,isnull( max( datalength( rtrim(convert(varchar(128),db_name(dbid))))) ,6)
)
,@charMaxLenCPUTime =
convert( varchar
,isnull( max( datalength( rtrim(convert(varchar(128),cpu)))) ,7)
)
,@charMaxLenDiskIO =
convert( varchar
,isnull( max( datalength( rtrim(convert(varchar(128),physical_io)))) ,6)
)
,@charMaxLenCommand =
convert( varchar
,isnull( max( datalength( rtrim(convert(varchar(128),cmd)))) ,7)
)
,@charMaxLenHostName =
convert( varchar
,isnull( max( datalength( rtrim(convert(varchar(128),hostname)))) ,8)
)
,@charMaxLenProgramName =
convert( varchar
,isnull( max( datalength( rtrim(convert(varchar(128),program_name)))) ,11)
)
,@charMaxLenLastBatch =
convert( varchar
,isnull( max( datalength( rtrim(convert(varchar(128),last_batch_char)))) ,9)
)
from
#tb1_sysprocesses
where
-- sid >= @sidlow
-- and sid <= @sidhigh
-- and
spid >= @spidlow
and spid <= @spidhigh
IF @loginame is null
BEGIN
--------Output the report.
SELECT count(spid) as Sessions,
null as databases,
sp.loginname as Login
into #results
from #tb1_sysprocesses sp
where spid >= @charspidlow
and spid <= @charspidhigh
--and loginname <> 'sa'
GROUP BY sp.loginname
-- Create list of databases to see how many each person is using
select count(db) as dbcount,
loginname
INTO #dbusernames
from (select distinct db_name(dbid) as db, loginname from #tb1_sysprocesses) small
GROUP BY loginname
-- Join to database names
UPDATE #results
SET databases = db.dbcount
FROM #results, #dbusernames db
WHERE #results.login = db.loginname
-- Return results
SELECT * from #results
END
IF @loginame is not null --Do the actual process killing
BEGIN
DECLARE kill_cursor SCROLL CURSOR
FOR
SELECT spid
from #tb1_sysprocesses sp
where spid >= @charspidlow
and spid <= @charspidhigh
and upper(loginname) = upper(@loginame)
--open the cursor
OPEN kill_cursor
--fetch first variables from cursor
FETCH FIRST FROM kill_cursor
INTO @current_process
PRINT 'Killing all processes for user ' + @loginame
--While there is no error fetching rows insert data into #data table.
WHILE @@FETCH_STATUS = 0
BEGIN
EXEC ('kill ' + @current_process)
Print 'Successfully killed process number ' + @current_process
FETCH NEXT FROM kill_cursor
INTO @current_process
END
PRINT 'All processes for ' + @loginame + ' have been killed'
CLOSE kill_cursor
DEALLOCATE kill_cursor
END
SET nocount on
LABEL_86RETURN:
if (object_id('tempdb..#tb1_sysprocesses') is not null)
BEGIN
drop table #tb1_sysprocesses
if @loginame is null
BEGIN
DROP TABLE #dbusernames
DROP TABLE #results
END
END
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
