Technical Article

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

Rate

Share

Share

Rate