Kill all connection to database

Question

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

dant12 SSCrazy Points: 2712 More actions · Answer 1

i often use something more simple

DECLARE @TARGET INT

WHILE 1=1

BEGIN

DECLARE KILLCONS CURSOR LOCAL FAST_FORWARD FOR

SELECT SPID FROM MASTER..SYSPROCESSES WHERE DBID = DB_ID('DB NAME') AND SPID >=50

OPEN KILLCONS

FETCH NEXT FROM KILLCONS INTO @TARGET

WHILE @@FETCH_STATUS = 0

BEGIN

EXEC ('KILL '+@TARGET)

FETCH NEXT FROM KILLCONS INTO @TARGET

END

CLOSE KILLCONS

DEALLOCATE KILLCONS

END

the infinite while is to take care of pesky applications that keep reconnecting, so you just manually break the loop when you feel like it

--
Thiago Dantas
@DantHimself

skra SSCommitted Points: 1614 More actions · Answer 2

You're right - this procedure would be much more simple if I used cursors as You did.

rktn_odin SSChasing Mays Points: 636 More actions · Answer 3

i use this:

ALTER DATABASE Northwind SET SINGLE_USER WITH ROLLBACK IMMEDIATE

EXEC sp_dboption 'Northwind', 'single user', 'FALSE'

Oliver Wootton SSC Veteran Points: 291 More actions · Answer 4

--Kill all users instantly

use master

go

alter database test set offline with rollback immediate

go

alter database test set online

go

akljfhnlaflkj SSC Guru Points: 76202 More actions · Answer 5

SSC Guru

Points: 76202

May 17, 2016 at 6:33 am

Thanks for the script.