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

Would you like to learn Liquibase? Enroll to my course on Udemy.

liquibase course promo

review

We use cookies

We use cookies on our website. Some of them are essential for the operation of the site, while others help us to improve this site and the user experience (tracking cookies). You can decide for yourself whether you want to allow cookies or not. Please note that if you reject them, you may not be able to use all the functionalities of the site.