Post reply

Drop Database with Active Connections

  • June 2, 2006 at 12:29 am

    #169089

    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

    #641363

    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

    #641874

    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