LLI Wiki — My Digital Knowledge Base

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

Mastodon LLIMastodon SocialFosstodon