LLI Wiki — My Digital Knowledge Base

SQL Server: kill the sessions of a database

Generate KILL statements for the sessions of a database and force the disconnection with ROLLBACK IMMEDIATE.

This script disconnects the sessions connected to a database. It generates one KILL statement per session, then switches the database to RESTRICTED_USER to disconnect the remaining non-administrator users before returning it to MULTI_USER.

Generate and run the KILL statements

DECLARE @DatabaseName nvarchar(50)
SET @DatabaseName = N'AppDb'	

DECLARE @SQL varchar(max)

SELECT COALESCE(@SQL,'') + 'Kill ' + Convert(varchar, SPId) + ';',*
FROM MASTER..SysProcesses
WHERE DBId = DB_ID(@DatabaseName) AND SPId <> @@SPId


SELECT @SQL = COALESCE(@SQL,'') + 'Kill ' + Convert(varchar, SPId) + ';'
FROM MASTER..SysProcesses
WHERE DBId = DB_ID(@DatabaseName) AND 
SPId <> @@SPId

SELECT @SQL 
EXEC(@SQL)

Force the disconnection with ROLLBACK IMMEDIATE

ALTER DATABASE [AppDb] 
    SET RESTRICTED_USER WITH ROLLBACK IMMEDIATE;
GO

ALTER DATABASE [AppDb] 
    SET MULTI_USER WITH ROLLBACK IMMEDIATE;
GO

Notes

Mastodon LLIMastodon SocialFosstodon