June 2, 2006 at 12:29 am
I am wondering if there is any command that would kill all the active connections on a database in T-SQL. Primarily I need to drop a database through my stored procedure and I want to kill any active connections before doing that as the database drop would fail if it has any active connections open.
Thanks
Prasad Bhogadi
www.inforaise.com
June 2, 2006 at 1:42 am
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
June 6, 2006 at 1:20 am
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
Julian Kuiters
juliankuiters.id.au
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply