Drop Database with Active Connections

Question

Viewing 2 posts - 1 through 3 (of 3 total)

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

Edward Jolley SSC Rookie Points: 49 More actions · Answer 1

This command will take your database offline and kill all sessions

USE master

go

ALTER DATABASE <your database name>

SET OFFLINE

WITH ROLLBACK IMMEDIATE

to bring the database back online

USE master

go

ALTER DATABASE <your database name>

SET ONLINE

And thats it

Jolley

Julian Kuiters SSCertifiable Points: 6585 More actions · Answer 2

Another method is

USE master

go

DECLARE @spid int

SELECT @spid = min(spid) from master.dbo.sysprocesses where dbid = db_id()

WHILE @spid IS NOT NULL

BEGIN

EXECUTE ('KILL ' + @spid)

SELECT @spid = min(spid) from master.dbo.sysprocesses where dbid = db_id() AND spid > @spid

END