January 7, 2015 at 1:20 am
Hi can anyone help me put together a script that would kill any process related to specific hostname, I assume a bat file can be schedule over night to run the script.
Thanks in advance.
January 7, 2015 at 2:16 am
You can get all the sessions that came from a specific host by querying sys.dm_exec_sessions and get the value of session_id according to the value of column host_name. When you get the sessions you can issue a kill statement with the sessions' numbers. There is no need to do it with a batch file, you can do it with a job.
Adi
--------------------------------------------------------------
To know how to ask questions and increase the chances of getting asnwers:
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
January 7, 2015 at 4:07 am
Adi Cohn-120898 (1/7/2015)
You can get all the sessions that came from a specific host by querying sys.dm_exec_sessions and get the value of session_id according to the value of column host_name. When you get the sessions you can issue a kill statement with the sessions' numbers. There is no need to do it with a batch file, you can do it with a job.Adi
Right I managed to get the list I want, how do I feed the ID's and kill them all?
SELECT session_id, host_name, status, cpu_time, memory_usage
FROM sys.dm_exec_sessions AS s
where s.host_name = 'MyHost' AND status = 'sleeping'
In Linux we can grep and run a command, I'm familiar with PHP as well but struggling with T-SQL.
January 7, 2015 at 6:10 am
This is one of the few times that cursor is helpful. You can create cursor for this SQL statement:
select 'kill ' + cast(session_id as varchar(20))
from sys.dm_exec_sessions
where host_name = 'MyHost' AND status = 'sleeping'
After that you create a loop, gets a record's value into a variable and then use this variable with an execute statement. If you never worked with a cursor, you can find lots of examples on the internet of how to work with it.
Adi
--------------------------------------------------------------
To know how to ask questions and increase the chances of getting asnwers:
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
January 7, 2015 at 6:26 am
Adi Cohn-120898 (1/7/2015)
This is one of the few times that cursor is helpful. You can create cursor for this SQL statement:
select 'kill ' + cast(session_id as varchar(20))
from sys.dm_exec_sessions
where host_name = 'MyHost' AND status = 'sleeping'
After that you create a loop, gets a record's value into a variable and then use this variable with an execute statement. If you never worked with a cursor, you can find lots of examples on the internet of how to work with it.
Adi
I've never done loops in SQL before, I will play around and see if I can figure it out, thanks for the help!
January 15, 2015 at 2:13 pm
You can use below query, it picks all the connections from a host (ABC in this case) from sys.sysprocesses; creates a common statement to kill them; and then executes the statement.
Replace 'ABC' with hostname;
you can see which sessions it has killed by deleting the comment from 'print' section of the script
declare @SqlCmd varchar(1000), @HostName varchar(100)
-- Set the hostname name from which to kill the connections
set @HostName = 'ABC'
set @SqlCmd = ''
select @Sqlcmd = @SqlCmd + 'kill ' + convert(char(10), spid) + ' '
from master.dbo.sysprocesses
where hostname= @HostName
and
DBID <> 0
and
spid <> @@spid
exec (@Sqlcmd)
--print @sqlcmd
GO
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply