CODEWITHSUNDEEP
sqlsql-serversql-server-2008
Matt
Matt

Reputation: 26971

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

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

Upvotes: 3

Views: 7635

Answers (2)

Michael Petrotta
Michael Petrotta

Reputation: 60902

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

Upvotes: 2

gbn
gbn

Reputation: 432200

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.

Upvotes: 9

Related Questions