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