kill a long running processes

Last Post 11 Jan 2012 02:21 PM by gunneyk. 7 Replies.
AddThis - Bookmarking and Sharing Button
Author Messages
ctseah
New Member
New Member

--
03 Jan 2012 02:36 AM

Hi,

In SQL2008, how can I find out a long running processes and kill it ?

Thanks in advance.

regards
rm
New Member
New Member

--
03 Jan 2012 06:22 AM
How do you define 'long running process'? Can kill it with 'kill spid' if you know spid of the process.
kenambrose
New Member
New Member

--
03 Jan 2012 11:33 AM
we use resource governor workload group to fire an alert when any query exceeds a certain cpu time, and service broker to respond to the alert by using the service broker "eventnotification" message type.

gunneyk
New Member
New Member

--
03 Jan 2012 01:31 PM
You cna use the DMV sys.dm_exec_requests to see what is currently running and look at the start time column. But just because it is running for a long time does not mean it should be killed. You need to determine rules that account for what may be normal.
ctseah
New Member
New Member

--
04 Jan 2012 10:42 PM

Hi,

Something the tempdev grows too big due to user input something absurd in the application layer to extract/process kind of 'out of the blue' information.
I need to identify such processes as an example. So that I can terminate or kill the spid.

Where specifically should I run this command 'kill spid' ?
Command prompt ? Do I need to go to any directory at the command prompt to execute this command ?
Is this an SQL2008 command or a Window OS command ?

Thanks
gunneyk
New Member
New Member

--
05 Jan 2012 06:03 AM
KILL is a TSQL command just like any other except you need to be sa to actually execute it. So you can use the Query editor in SSMS, oSql etc. Just remember that if the user is in the middle of a large transaction and you kill it the process will have to be rolled back so account for that as well.
ctseah
New Member
New Member

--
10 Jan 2012 07:26 PM

Hi,

I see, ok.
But running sys.dm_exec_requests does not show the spid.
Does it ?

thanks
gunneyk
New Member
New Member

--
11 Jan 2012 02:21 PM
If the SPID is actively doing something then yes it will. You can also look at sys.dm_exec_sessions to see things like Login time, last request time and total elapsed time etc. but just because a session has been open for x period of time does not mean it needs to be killed. That is where you need to determine the criteria for what is a rogue connection.


Acceptable Use Policy
---