How to kill a session id after getting its session id' value from XML ?

Question

Hello,

I would like to seek your help on how to kill a session id after I get its value from XML ? I have the following code which gives me the session_ids from the sys.dm_exec_sessions, and I load its Session_id values into XML as shown

declare @result varchar(1000) = 
     (
         select session_id as session_id,
                login_time as login_time,
                login_name as login_name,
                host_name as host_name,
                program_name as program_name,
               client_interface_name as client_interface_name
         from sys.dm_exec_sessions
         where program_name not like 'SqlQueryNoti%' and host_name
                like 'Machine1' and database_id = 1


         for xml path('i'), root('d')
     )

The next step is that I want to kill session_ids from the above xml. I found a separate code for killing the session_id
as shown

USE [master];

  DECLARE @kill varchar(8000) = '';  
  SELECT @kill = @kill + 'kill ' + CONVERT(varchar(5), session_id) + ';'  
  FROM sys.dm_exec_sessions
  WHERE database_id  = 8

  EXEC(@kill);

My question is that I would like to merge the two codes together but I don't know or don't understand how so I'd like to seek your expertise and helps?

Many Thanks for the helps,
Du

Answer 1

Answer:

hi,

Why do you need all the information as XML when your only goal is to get the session ID in order to kill it?

You have no need for XML. Simply declare a variable type INT and insert to it only the session id

pseudo code for example

DECLARE @SESSION_ID INT  
SELECT TOP 1 @SESSION_ID = session_id FROM sys.dm_exec_sessions  
WHERE ....  
KILL @SESSION_ID  

You have a great explanation in the official documentation about killing a session in this doc:

https://learn.microsoft.com/en-us/sql/t-sql/language-elements/kill-transact-sql

Please inform us if you need did not succeed and you need some more info, but I think that the document with this short answer cover what you need :-)

Off-topic

Why do you use alias name for the columns in the result SET when the original names are the same?!?

This code make not a lot of sense

session_id as session_id,  
login_time as login_time,  
login_name as login_name,  
host_name as host_name,  
program_name as program_name,  
client_interface_name as client_interface_name  

You need to use alias name when the result SET does not have a name for the column - for example when you use aggregate function like MIN(Column_Name), or you use alias name when you have a good reason to change the name which returns from the query - for example you want (for some reason) to get pretty like RonenColumn instead of an original name like vtwetvuwytovewrtyvuerwytuvh5402h32v53 so in this case you use vtwetvuwytovewrtyvuerwytuvh5402h32v53 as RonenColumn

The upper part of the code has no such need, since you replace the original name with the exact same alias name, so you can simply use the original name

session_id,  
login_time,  
login_name,  
host_name,  
program_name,  
client_interface_name

Answer 2

As Ronen says, it makes little sense to first package the data in XML and then to extract data from it. We have a Swedish proverb to describe thiis: Walk across the stream to get water.

If you want to kill all processes that matches the query blindly, you can do this:

DECLARE @sql nvarchar(MAX)
SELECT @sql = string_agg('KILL ' + convert(varchar(10), session_id), char(13) + char(10))
FROM   sys.dm_exec_sessions
WHERE  program_name NOT LIKE 'SqlQueryNoti%'
   and host_name LIKE 'Machine1' 
   and database_id = 1

PRINT @sql
EXEC(@sql)

If you want to inspect the data first and only kill selected processes, run your query above, but without the XML packaging. Then copy the result from the grid to the query window. Delete the lines for the processes you want to spare. Then place the cursor in col 1 on the first line in the result set. Press Alt+Shift and keep it pressed while you press the down-arrow key to the end of the result set. Now type KILL. Because you have enabled rectangular editing, KILL appear on all lines. Next place the cursor on the first line again, but now after the session idea. Press Alt+Shift again and move with the down-arrow key to the end. Type -- to comment out the rest of the lines. Then select the lines in normal way, and press Execute.

Answer 3

Thank you so much for all of the help. I learn a lot from you.

May I ask get your helps regarding my situation ? I was asked to provide names of the processes running on Machine1 so that I tried to print them out from XML. After providing names , I am asked to execute the kill command to process which program_name not like data mining process. So I have two pieces of codes. One piece of code for print out name of processes in XML , and another one for killing process that is not a data mining process.
I understand tat the demand it is not logical but i was asked to do it. I get stuck in executing the kill from the xml. I don't know on how to parse each node of the xml tree and execute a kill on process that has program_name not like data mining.
I am sorry for asking a non logical question.
Many Thanks,
Du