Generate KILL statements for the sessions of one database
Build KILL statements for every session connected to a SQL Server database, review them, run them and check the sessions still connected.
Disconnecting every session from one database, for example before a maintenance operation, can be done by generating one KILL statement per session. The first step only builds the statements, so they can be reviewed before they run.
Generate the KILL statements
DECLARE @DatabaseName nvarchar(50)
SET @DatabaseName = N'MyDatabase'
DECLARE @SQL varchar(max)
SELECT @SQL = COALESCE(@SQL,'') + 'Kill ' + Convert(varchar, SPId) + ';'
FROM MASTER..SysProcesses
WHERE DBId = DB_ID(@DatabaseName) AND
SPId <> @@SPId
SELECT @SQL
EXEC(@SQL)
The SELECT @SQL displays the statements. The EXEC line runs them. Review the displayed list before running the EXEC.
Check the sessions of a database
SELECT DB_NAME(S.dbid) ,*
FROM sys.sysprocesses S
OUTER APPLY sys.dm_exec_sql_text(S.sql_handle) AS Q
-- OUTER APPLY sys.dm_exec_query_plan(plan_handle)
WHERE DB_NAME(S.dbid) LIKE '%MyDatabase%'
Notes
KILLends the session and rolls back its open transaction. A long transaction can take time to roll back.- The statement excludes the current session (
@@SPID).