What is the SQL command to forcibly close all other connections to the a database

Question

What is the SQL command to forcibly close all other connections to the a database. This is for SQL Server 2008

Answer 1

One way using ROLLBACK IMMEDIATE

ALTER DATABASE foo SET SINGLE_USER WITH ROLLBACK IMMEDIATE;

You can use KILL but you want to prevent re-connections too.

Answer 2

Use the Kill statement to close a single connection:

Terminates a user process that is based on the session ID (SPID in SQL Server 2000 and earlier)

Without further comment, here's a stored procedure we've used off and on over the years, to close all connections, using kill. I'm sure there's a better/non-cursor way to do this.

CREATE PROCEDURE kill_database_users @arg_dbname sysname
AS

declare @a_spid smallint
declare @msg    varchar(255)
declare @a_dbid int

select
    @a_dbid = sdb.dbid
from    master..sysdatabases sdb
where   sdb.name = @arg_dbname

declare db_users insensitive cursor for
select
    sp.spid
from    master..sysprocesses sp
where   sp.dbid = @a_dbid

open db_users

fetch next from db_users into @a_spid
while @@fetch_status = 0
    begin
    select @msg = 'kill '+convert(char(5),@a_spid)
    print @msg
    execute (@msg)
    fetch next from db_users into @a_spid
    end

close db_users
deallocate db_users

Answer 3

Check related question

http://stackoverflow.com/questions/11620/how-do-you-kill-all-current-connections-to-a-sql-server-2005-database

EDIT Useful link, that wasn't mentioned in the accepted answer

http://wiki.lessthandot.com/index.php/Kill_All_Active_Connections_To_A_Database