Monitoring blocking...

Is there a way to be notified automatically when blocking
occurs (either via net send or e-mail and maybe even have
that written to the NT event log)?
ThanksHi Rob
Blocking happens all the time, but it is usually for so short a period of
time that if you had a notification every time you thing there was another
spam virus. There is no automatic way to e notified about blocking, but here
are some avenues you might consider:
Set up a script to run a loop, that captures sysprocesses output and saves
it in a table. Compare the waittime column from one run to the next, if the
blocked column is >0. If the waittime is above some threshhold, send a
notification.
Set lock timeout to 0 for each connection, so that any time a block occurs,
the process will get an error 1222 and stop. You will have to add code to
retry if needed. You can then set up an alert to notify you when error 1222
occurs, or you could change the definition of error 1222 to include writing
it to the event log. Getting every session to set the lock timeout value may
require changing a lot of application code.
--
HTH
--
Kalen Delaney
SQL Server MVP
www.SolidQualityLearning.com
"Rob" <rhchin@.hotmail.com> wrote in message
news:02ba01c38f5a$285b5a50$a101280a@.phx.gbl...
> Is there a way to be notified automatically when blocking
> occurs (either via net send or e-mail and maybe even have
> that written to the NT event log)?
> Thanks|||Oops, I also meant to suggest you take a look at these KB articles:
http://support.microsoft.com/default.aspx?scid=kb;en-us;271509&Product=sql2k
INF: How to Monitor SQL Server 2000 Blocking
http://support.microsoft.com/default.aspx?scid=kb;en-us;283725&Product=sql2k
INF: How to View SQL Server 2000 Blocking Data
HTH
--
Kalen Delaney
SQL Server MVP
www.SolidQualityLearning.com
"Kalen Delaney" <replies@.public_newsgroups.com> wrote in message
news:uOe6jK2jDHA.2964@.tk2msftngp13.phx.gbl...
> Hi Rob
> Blocking happens all the time, but it is usually for so short a period of
> time that if you had a notification every time you thing there was another
> spam virus. There is no automatic way to e notified about blocking, but
here
> are some avenues you might consider:
> Set up a script to run a loop, that captures sysprocesses output and saves
> it in a table. Compare the waittime column from one run to the next, if
the
> blocked column is >0. If the waittime is above some threshhold, send a
> notification.
> Set lock timeout to 0 for each connection, so that any time a block
occurs,
> the process will get an error 1222 and stop. You will have to add code to
> retry if needed. You can then set up an alert to notify you when error
1222
> occurs, or you could change the definition of error 1222 to include
writing
> it to the event log. Getting every session to set the lock timeout value
may
> require changing a lot of application code.
> --
> HTH
> --
> Kalen Delaney
> SQL Server MVP
> www.SolidQualityLearning.com
>
> "Rob" <rhchin@.hotmail.com> wrote in message
> news:02ba01c38f5a$285b5a50$a101280a@.phx.gbl...
> > Is there a way to be notified automatically when blocking
> > occurs (either via net send or e-mail and maybe even have
> > that written to the NT event log)?
> >
> > Thanks
>