Thursday, March 8, 2012

Blocking

Is there a way to setup SQL server to automatically kill a
blocking process after a certain time frame (after maybe 5
Minutes for example)?
We are running SQL Server 2000 service pack 3a.
I second this question! Anyone have an easy solution for this?
will
"Wendy" wrote:

> Is there a way to setup SQL server to automatically kill a
> blocking process after a certain time frame (after maybe 5
> Minutes for example)?
> We are running SQL Server 2000 service pack 3a.
>
|||There is no way to do this automatically in SQL Server 2000. You can set up
a job that inspects sysprocesses periodically, looking at who is blocked,
and the wait_time, and then see who has blocked them. But you'll want to see
if the blocker is also being blocked, before indiscriminately issuing KILLs.
In fact, anything 'automatic' might have a problem with killing the 'wrong'
process.
SQL 2005 allows you to configure a 'blocked process threshold', to generate
an event when someone is blocked longer than the amount of time you
configure. So it will save you the periodic scanning of sysprocesses, but
you'll still have to apply some logic to figure out who you want to KILL (if
anyone) when a process does exceed that threshold.
HTH
Kalen Delaney, SQL Server MVP
www.solidqualitylearning.com
"we7313" <we7313@.discussions.microsoft.com> wrote in message
news:D235A6DC-59B4-4997-B376-146D8666DE9A@.microsoft.com...
>I second this question! Anyone have an easy solution for this?
> --
> will
>
> "Wendy" wrote:
>

No comments:

Post a Comment