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 maint.KillConnections(@database VARCHAR(50)) AS BEGIN
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 FAST_FORWARD FOR SELECT request_session_id
FROM sys.dm_tran_locks
WHERE resource_type='DATABASE'
AND (DB_NAME(resource_database_id)=@database OR @database IS NULL)
OPEN c1
FETCH c1 INTO @spid
WHILE @@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 @spid
END
-- Clean up
CLOSE c1
DEALLOCATE c1
END
GO
