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
- The first
SELECTonly displays the generated statements. Only the second block fills@SQL. ROLLBACK IMMEDIATErolls back the open transactions of the affected sessions at once.