MS SQL Server - How to query lock contention

Author

How to query lock contention

Mark Butler

2005-06-29, 11:23 am

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

Ravi

2005-06-29, 11:23 am

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
>
>
>

Sponsored Links