Why can't I kill blocking processes?

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

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

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
>

I can't believe this is the problem -- the locks are on SELECT statements,
there's nothing to roll back.

"SkyWalker" wrote:

> 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:
> 

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:

> I can't believe this is the problem -- the locks are on SELECT statements,
> there's nothing to roll back.
>
> "SkyWalker" wrote:
> 

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