Each SQL Server database administrator and database developer has a set of scripts that should always be somewhere close. One of them is the one that kills a bunch of connections to a database. It can be a group of all connections, those made by a particular login or the ones using a specific database. No matter what actual conditions are, killing dozens or hundreds connections manually is not fun. Here are provide an SQL script that can be easily customized to cover a specific need. The below version destroys all non-background connections to a database called MyDatabase.
DECLARE @dbName varchar(200) = 'MyDatabase';
DECLARE @sql varchar(200);
DECLARE statements CURSOR FAST_FORWARD FOR
SELECT 'kill ' + CAST(spid as varchar(200))
FROM sys.sysprocesses
WHERE status <> 'background'
AND dbid = DB_ID(@dbName);
OPEN statements;
FETCH NEXT FROM statements INTO @sql;
WHILE @@FETCH_STATUS = 0 BEGIN
PRINT @sql;
EXECUTE(@sql);
FETCH NEXT FROM statements INTO @sql;
END;
CLOSE statements;
DEALLOCATE statements;
GO
