Search code examples
sql-serversqlcmd

Run kill command from sqlcmd

I am trying to kill the active session in the following way... 

C:\>sqlcmd -E -S %COMPUTERNAME%\INSTANCE -Q "kill (SELECT session_id FROM sys
.dm_exec_sessions WHERE login_name = 'dbuser')"

How to make it work...

Solution

  • KILL requires a session id literal so you need to build and execute a script with the needed KILL commands. The method below will also handle multiple sessions.

    sqlcmd -E -S . -Q "DECLARE @KillCommands nvarchar(MAX) = (SELECT 'KILL ' + CAST(session_id AS varchar(5)) + ';' FROM sys.dm_exec_sessions WHERE login_name = 'dbuser' FOR XML PATH(''));EXEC(@KillCommands);"