Stored Procedure to kill all connections to selected database or server

SQL Stored Procedure to kill all connections to selected database or server except for the process 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 exclusive mode by the restore task.

The code below will accept a database name, so it only needs to be written once and stored on the master or utilities (if you have created one) database. It can then be called from any other process, in our case the complete maintenance plan in the document above this.

It is a fairly simple stored procedures that simply gets a list of the active connections and loops through each one killing the process and connection.

SQL Code

CREATE PROC KillConnections(@database VARCHAR(50))AS BEGINSET NOCOUNT ON;DECLARE @spid INTDECLARE @killstatement NVARCHAR(10)--Declare a cursor to select the users connected to the specified databaseDECLARE c1 CURSOR FAST_FORWARD FOR SELECT request_session_id                    FROM sys.dm_tran_locksWHERE resource_type='DATABASE'AND(DB_NAME(resource_database_id)=@database OR @database IS NULL)OPEN c1FETCH c1 INTO @spidWHILE @@FETCH_STATUS=0 BEGIN     IF @@SPID<>@spid--Don't kill the connection of the user executing this statement     BEGIN           -- Construct dynamic sql to kill spid           SET @killstatement='KILL '+CAST(@spid AS VARCHAR(5))           EXEC sp_executesql @killstatement           PRINT @spid-- Print killed spid                 END     FETCH NEXT FROM c1 INTO @spidEND-- Clean upCLOSE c1DEALLOCATE c1ENDGO