Script to kill all database connections

killConnectionsEach 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

Do not miss valuable content. You will receive a monthly summary email. You can unsubscribe anytime.

If you like what I do, consider buying me a coffee :)

Buy me a coffeeBuy me a coffee