Kill all connection to database

use master

go

if exists(select * from master.dbo.sysobjects where name = 'proc_kill_dbconnections' and type = 'P')

    drop procedure dbo.proc_kill_dbconnections

go

create procedure proc_kill_dbconnections

/*****************************************************************************************************

*

* Author Rafal Skotak

* Purpose Procedure tries to kill all connections to the specified database

*

* Date 2011.02.18

*

******************************************************************************************************/

    @dbname    sysname

with encryption

as

begin

    set nocount on

    ------- prepare temporary tables

    -------------------------------------------

    create table #temp_processes_table

    (

        spid             int primary key,

        ecid             int,

        status             sysname,

        loginame         sysname null,

        hostname         sysname null,

        blk             int,

        dbname             sysname null,

        cmd             sysname null,

        request_id        int null,

        mod             int not null default 0

    );

    -- get processes list

    --------------------------

    insert into #temp_processes_table (spid, ecid, status, loginame, hostname, blk, dbname, cmd, request_id) exec sp_who

    declare @count        int

    declare @process_id    int

    declare @cmd        varchar(8000)

    select @count = count(*) from #temp_processes_table where mod = 0

    while @count > 0

    begin

        set @process_id = NULL

        select top 1 @process_id = spid from #temp_processes_table where mod = 0 and dbname = @dbname

        if @process_id is NULL

            break

        print @process_id

        set @cmd = 'kill ' + cast(@process_id as varchar)

        exec (@cmd)

        update #temp_processes_table set mod = 1 where spid = @process_id

        select @count = count(*) from #temp_processes_table where mod = 0 and dbname = @dbname

    end

    -- cleanup - drop temporary tables

    ---------------------------------------

    drop table #temp_processes_table

end

go

-- example

-- exec dbo.proc_kill_dbconnections 'Northwind'