⚠️ SQL server: Terminate database connections

⚠️ SQL server: Terminate database connections

Each application taht uses your SQL Server database creates a connection on the database and this connection continues unless the session is killed or terminated privately within the application. A similar connection is established even for each query window in SQL Server Management Studio.

These active connections may cause us problems in many processes, especially restoration operations. Looped applications can occupy our server unnecessarily. We may need to terminate it specifically.

In such cases, all connections installed in that database are terminated with the following script. It is enough to write the name of our database, and which connections we want to terminate.

USE master
DECLARE @dbname sysname
SET @dbname = '<dbnamehere>'
DECLARE @spid int
SELECT @spid = min (spid) from master.dbo.sysprocesses where dbid = db_id (@dbname)
WHILE @spid IS NOT NULL
BEGIN
EXECUTE ('KILL' + @spid)
SELECT @spid = min (spid) from master.dbo.sysprocesses where dbid = db_id (@dbname) AND spid> @spid
END

Hopefully it benefits your business…

Written by
Stefan Pejcic
Join the discussion

I enjoy constructive responses and professional comments to my posts, and invite anyone to comment or link to my site.