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);
       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;
       PRINT @sql;
       FETCH NEXT FROM statements INTO @sql;

CLOSE statements;
DEALLOCATE statements;

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

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.