Lock Query

How do you find what user is locking what row in what table in what database
?On Thu, 27 Oct 2005 13:17:01 -0700, CLM
<CLM@.discussions.microsoft.com> wrote:[vbcol=seagreen]
>How do you find what user is locking what row in what table in what database?[/vbco
l]
exec sp_who2 is a start.
I don't know of any way to map to individual rows, though it would be
nice!
What do you need it for?
J.|||CLM wrote:
> How do you find what user is locking what row in what table in what
> database?
Might not be a row. Could be a page or the entire table.
You can run sp_lock to a get a list of locks on the server. Turn the
dboid into a database name using db_name(dbid). Turn the ObjId into a
name using object_name(ObjId) from the correct database. Turn the spid
into a user by selecting the spid from master..sysprocesses.
David Gugick
Quest Software
www.imceda.com
www.quest.com|||I had some informix guys ask me. They can find out exactly what row is bein
g
locked which I know has been useful on the production informix side of the
world. I figured it would be even easier on the Sql Server side. You can
find a lot from sp_lock but not which row...
"jxstern" wrote:

> On Thu, 27 Oct 2005 13:17:01 -0700, CLM
> <CLM@.discussions.microsoft.com> wrote:
> exec sp_who2 is a start.
> I don't know of any way to map to individual rows, though it would be
> nice!
> What do you need it for?
> J.
>|||David Gugick wrote:
> CLM wrote:
> Might not be a row. Could be a page or the entire table.
> You can run sp_lock to a get a list of locks on the server. Turn the
> dboid into a database name using db_name(dbid). Turn the ObjId into a
> name using object_name(ObjId) from the correct database. Turn the spid
> into a user by selecting the spid from master..sysprocesses.
You can also use DBCC OPENTRAN and fn_get_sql to get information about
what is running for the spid that is blocking.
See if this page helps:
http://support.microsoft.com/defaul...kb;en-us;224453
David Gugick
Quest Software
www.imceda.com
www.quest.com