Cascading Blocking ?

[Ryan]

We have a situation that occurs every so often with blocking of
various databases on one server (Win200 SQL7). It appears to happen at
random, so I'm assuming it originates from something a user does and
not a regularily run process.

We've examined the data available to us and used the very helpful
blocking code on http://www.algonet.se/~sommar/sqlutil/aba_lockinfo.html
(Thanks Erland).

Were getting closer to finding the problem, but need some advice on
what to look for.

This is a bit of guesswork, but we suspect that we get into a
situation where blocking takes places, and this then cascades to other
processes which then block others in turn. The original culprit then
finishes, but the blocks continue as the newer processes are holding
something else up. A bit like dominoes. It seems to take a while to
free this up.

The problem we have is determining the start of this process. Once we
are made aware of blocking issues, we can find out who is doing what,
but almost always get a different answer/user and think we're getting
to it a little late.

Ideally, I want to log the blocking somewhere so I can examine the
files when this occurs and can therefore establish a pattern etc...

Any ideas or suggestions would be welcome.

[Dan Guzman]

Take a look at
http://support.microsoft.com/default...b;EN-US;251004.

--
Hope this helps.

Dan Guzman
SQL Server MVP

-----------------------
SQL FAQ links (courtesy Neil Pike):

http://www.ntfaq.com/Articles/Index....partmentID=800
http://www.sqlserverfaq.com
http://www.mssqlserver.com/faq
-----------------------

"Ryan" <ry********@hotmail.com> wrote in message
news:78*************************@posting.google.co m...

We have a situation that occurs every so often with blocking of
various databases on one server (Win200 SQL7). It appears to happen at
random, so I'm assuming it originates from something a user does and
not a regularily run process.

We've examined the data available to us and used the very helpful
blocking code on http://www.algonet.se/~sommar/sqlutil/aba_lockinfo.html (Thanks Erland).

Were getting closer to finding the problem, but need some advice on
what to look for.

This is a bit of guesswork, but we suspect that we get into a
situation where blocking takes places, and this then cascades to other
processes which then block others in turn. The original culprit then
finishes, but the blocks continue as the newer processes are holding
something else up. A bit like dominoes. It seems to take a while to
free this up.

The problem we have is determining the start of this process. Once we
are made aware of blocking issues, we can find out who is doing what,
but almost always get a different answer/user and think we're getting
to it a little late.

Ideally, I want to log the blocking somewhere so I can examine the
files when this occurs and can therefore establish a pattern etc...

Any ideas or suggestions would be welcome.