How to query lock contention

Is there a way to query SQL 2000 to find out about table/database lock
contention. I have an application that is getting locked out many times a
day. I can see using "sp_who2" that various spids are being blocked by
others, which in turn are blocked by another...
If I "kill" the top level spid the system frees. I would like to find out
what table that process was locking or holding onto that was causing the
other workstations to hang. Are there any statistics or tools that would
help me?
TIA
Mark
Try the stored procedure sp_lock
Thanks
Ravi
"Mark Butler" wrote:

> Is there a way to query SQL 2000 to find out about table/database lock
> contention. I have an application that is getting locked out many times a
> day. I can see using "sp_who2" that various spids are being blocked by
> others, which in turn are blocked by another...
> If I "kill" the top level spid the system frees. I would like to find out
> what table that process was locking or holding onto that was causing the
> other workstations to hang. Are there any statistics or tools that would
> help me?
> TIA
> Mark
>
>