Lock Information

I need some help in interpreting the information displayed under
Management -> Current Activity -> Locks / Process ID in Enterprise
Manager.
There seems to be a number of locks against the "Water.dbo.sched_lock"
object. The values in the Index column are different for the different
rows: "Gazetteer", "Employees" and "pk_workbank...".
The strange thing is that these are not indexes of the Sched_Lock
table. Gazetteer and Employees refer to 2 different tables within the
database.
Why are they being displayed against a lock which is supposed to be
held against the "sched_lock" table?No ideas, anyone?|||I never use that to monitor locks. A few years ago there
were posts like yours so I played around with viewing locks
from Enterprise Manager and found that it's pretty easy to
get bogus, useless info. I specifically remember seeing the
wrong table references. And then if you look at the stored
procedure used by Enterprise Manager,
sp_MSset_currency_activity, you can see how the results
wouldn't be too reliable.
Use sp_lock, sp_who2 to monitor things. Or even better is
the stored procedure Erland wrote:
http://www.sommarskog.se/sqlutil/aba_lockinfo.html
-Sue
On 7 Sep 2006 09:39:26 -0700, "Paul H"
<del.monte@.ntlworld.com> wrote:

>I need some help in interpreting the information displayed under
>Management -> Current Activity -> Locks / Process ID in Enterprise
>Manager.
>There seems to be a number of locks against the "Water.dbo.sched_lock"
>object. The values in the Index column are different for the different
>rows: "Gazetteer", "Employees" and "pk_workbank...".
>The strange thing is that these are not indexes of the Sched_Lock
>table. Gazetteer and Employees refer to 2 different tables within the
>database.
>Why are they being displayed against a lock which is supposed to be
>held against the "sched_lock" table?|||Thanks very much Sue - it's a pain that a part of Enterprise Manager
itself would be unreliable.
I've had a look at sp_MSset_current_activity, and I've seen where the
Index column is likely to get mixed up. I can't see why the Table
column would be unreliable though - it seems like a fair bet to me that
the data in the table column is correct, and the locks are being held
against the "sched_lock" table. Would you agree?|||In your case, yes.
In terms of the other areas of unreliability in that stored
procedure, look at the isolation levels being used.
Many GUI tools for databases are have issues - not just
Microsoft's. You're often better off just knowing the SQL
commands to execute. It generally gives you more flexibility
in writing some of your own scripts to retrieve the data you
want, with the information you need or care about.
-Sue
On 11 Sep 2006 08:21:22 -0700, "Paul H"
<del.monte@.ntlworld.com> wrote:

>Thanks very much Sue - it's a pain that a part of Enterprise Manager
>itself would be unreliable.
>I've had a look at sp_MSset_current_activity, and I've seen where the
>Index column is likely to get mixed up. I can't see why the Table
>column would be unreliable though - it seems like a fair bet to me that
>the data in the table column is correct, and the locks are being held
>against the "sched_lock" table. Would you agree?|||Indeed.
Thanks very much.