August 14, 2008 at 3:00 pm
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.
August 14, 2008 at 3:03 pm
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
August 14, 2008 at 3:05 pm
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
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply