SQL Stored Procedure to Kill all Connections to Selected Database

SQL Stored Procedure to Kill all Connections to Selected Database except for the person calling it.

Introduction

There can be times when you need to disconnect all users from your database, one example is for Log Shipping replication. This code below was used on a reporting server which was updated every 20 minutes, as the database needs to be opened in exlusive mode by the restore task.
I will try and create an article on setting up a reporting database in the near future.
 
I did find some of this code elsewhere, however I can not find the article any more, if it is yours then please let me know.

The code below will accept a database name, so it only needs to be written once and stored on the master database. It can then be called from any other process, in our case the complete maintenance plan.

SQL Code

Use master

GO

CREATE PROC KillConnections(@database varchar(50)) AS

set nocount on

declare @spid int

declare @killstatement nvarchar(10)

-- Declare a cursor to select the users connected to the specified database

declare c1 cursor for select request_session_id from sys.dm_tran_locks where resource_type='DATABASE' AND DB_NAME(resource_database_id)= @database

open c1

fetch next from c1 into @spid

-- for each spid...

while @@FETCH_STATUS= 0

begin

      -- Don't kill the connection of the user executing this statement

      IF @@SPID<>@spid

      begin

            -- Construct dynamic sql to kill spid

            set @killstatement ='KILL '+cast(@spid asvarchar(3))

            exec sp_executesql @killstatement

            -- Print killed spid

            print @spid

      end

      fetch next from c1 into @spid

end

-- Clean up

close c1

deallocate c1

Ousia Logo