Technical Article

Kill all connection to database

,

Procedure tries to kill all connections to the specified database.

Example of usage:

exec dbo.proc_kill_dbconnections 'Northwind'

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'

Read 32,968 times
(81 in last 30 days)

Rate

2.71 (7)

You rated this post out of 5. Change rating

Share

Share

Rate

2.71 (7)

You rated this post out of 5. Change rating