Find blocking process

Hi,
we're having a problem with SQL 2000 and Opta 2000 JDBC driver
where there is large update running and at the same time,
read is blocked for a while.
We're looking for a way to catch this blocking process
and if it last more than 10 minutes, then email or send out a message.
I know sp_lock returns all current locks
but how do you know which one is blocking other processes?

Thanks for your help in advance.neo (second714@.hotmail.com) writes:
> we're having a problem with SQL 2000 and Opta 2000 JDBC driver
> where there is large update running and at the same time,
> read is blocked for a while.
> We're looking for a way to catch this blocking process
> and if it last more than 10 minutes, then email or send out a message.
> I know sp_lock returns all current locks
> but how do you know which one is blocking other processes?

The simplest way is to use sp_who. If a process is blocked, you will
see a non-zero value in the Blk column. This is the spid of the blocker.

--
Erland Sommarskog, SQL Server MVP, sommar@.algonet.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp