|
Home > Archive > MS SQL Server > May 2005 > Why can't I kill blocking processes?
You are viewing an archived Text-only version of the thread.
To view this thread in it's original format and/or if you want to reply to
this thread please [click here]
| Author |
Why can't I kill blocking processes?
|
|
| Maury Markowitz 2005-05-31, 8:23 pm |
| Periodically our server gets into a "state", typically because Access died
with a query running. The result is a blocking process, typically on a
heavily used table.
In theory I should just kill that process, but it never works. I click on it
to view, hit Kill Process, and... nothing.
Does this feature not work, or am I misinterpreting what it's supposed to do?
Maury
| |
| pdxJaxon 2005-05-31, 8:23 pm |
| probably just Enterprise manager not behaving correctly
do this in Query Analyzer
1. Run sp_Who2
2. Identify the spid of the offending process
3. Run KILL # (where # is the spid number in question)
Greg Jackson
PDX, Oregon
| |
| Mike Epprecht \(SQL MVP\) 2005-05-31, 8:23 pm |
| And if the process ran for 10 minutes, expect the kill to take at least as
long as it has to roll back all the uncommitted transactions.
Regards
--------------------------------
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
IM: mike@epprecht.net
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"pdxJaxon" < GregoryAJackson@Hotm
ail.com> wrote in message
news:uM$X7HjZFHA.612@TK2MSFTNGP12.phx.gbl...
> probably just Enterprise manager not behaving correctly
>
> do this in Query Analyzer
>
> 1. Run sp_Who2
> 2. Identify the spid of the offending process
> 3. Run KILL # (where # is the spid number in question)
>
>
> Greg Jackson
> PDX, Oregon
>
| |
| Maury Markowitz 2005-05-31, 8:23 pm |
| I can't believe this is the problem -- the locks are on SELECT statements,
there's nothing to roll back.
"SkyWalker" wrote:
[color=darkred]
> When you're killing some process, server needs some time to roll it back.
> If you would run kill command to the same process or better
> kill ... WITH STATUSONLY server will give you an estimations
> on how long it will take and percentage that has been done.
> It would be estimation only though.
>
> Regards.
>
> "Maury Markowitz" wrote:
>
| |
| SkyWalker 2005-05-31, 8:23 pm |
| When you kill the process it will not be physically
terminated, but will be marked to be terminated.
Then it can take for a while depends on current activity
of this process, server activity, etc.
There is a detail info on how SQL server kill command works:
http://support.microsoft.com/defaul...kb;en-us;171224
Regards.
"Maury Markowitz" wrote:
[color=darkred]
> I can't believe this is the problem -- the locks are on SELECT statements,
> there's nothing to roll back.
>
> "SkyWalker" wrote:
>
| |
| SkyWalker 2005-05-31, 8:23 pm |
| When you're killing some process, server needs some time to roll it back.
If you would run kill command to the same process or better
kill ... WITH STATUSONLY server will give you an estimations
on how long it will take and percentage that has been done.
It would be estimation only though.
Regards.
"Maury Markowitz" wrote:
> Periodically our server gets into a "state", typically because Access died
> with a query running. The result is a blocking process, typically on a
> heavily used table.
>
> In theory I should just kill that process, but it never works. I click on it
> to view, hit Kill Process, and... nothing.
>
> Does this feature not work, or am I misinterpreting what it's supposed to do?
>
> Maury
|
|
|
|
|