Drop All Connections to a Microsoft SQL Server Database

Microsoft SQL Server 2008 - Drop All Connections to a Database

In order to drop all active connections to a Microsoft SQL Server 2008 Database, run the following query, replacing 'The database name to close all active connections' to the name of the database to close all active connections:

DECLARE @databaseId as INTEGER
SET @databaseId = DB_ID('The database name to close all active connections')

DECLARE @sysProcessId as INTEGER
SELECT TOP 1 @sysProcessId = spid FROM master.dbo.sysprocesses WHERE dbid = @databaseId

WHILE @sysProcessId IS NOT NULL
BEGIN
    EXECUTE ('KILL ' + @sysProcessId)
    SET @sysProcessId = NULL
    SELECT TOP 1 @sysProcessId = spid FROM master.dbo.sysprocesses WHERE dbid = @databaseId
END