how to kill multiple SPIDs?

• SQL ORACLE

  One Orange Chip

  Points: 27807

  More actions

  August 14, 2008 at 3:00 pm

  #195901

  Sometimes, I need to kill more than one spid. It is so boring to kill spid one by one.

  Is there any syntax we can use to kill multiple SPIDs, such as

  KILL 53, 54

  ?

  Any input will be greatly appreciated in advance.

• GSquared

  SSC Guru

  Points: 260824

  More actions

  August 14, 2008 at 3:03 pm

  #858429

  Kill them one at a time.

  Or write a script like:

  Kill 1;

  Kill 2;

  Kill 3;

  and then run it all at once.

  - Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
  Property of The Thread

  "Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon

• Paul Timmerman

  Old Hand

  Points: 385

  More actions

  August 14, 2008 at 3:05 pm

  #858432

  I usethe following to KILL all connections to a given DB. Might be overkill for what you need.

  CREATE PROC dbo.smKillUsersInDB

  @DBName VARCHAR(20)

  AS

  SET NOCOUNT ON

  DECLARE

  @ID INT

  ,@MSG VARCHAR(10)

  WHILE 1 = 1

  BEGIN

  SET RowCount 1

  SELECT @ID = spid

  FROM master..sysprocesses P,

  master..sysdatabases D

  WHERE

  D.[name]=@DBName

  AND

  D.dbid = P.dbid

  IF @@ROWCOUNT = 0

  BREAK

  SELECT @MSG = 'KILL ' + convert(char(8) ,@ID)

  EXEC(@MSG)

  END