Results 1 to 5 of 5
  1. #1
    Join Date
    Feb 2004
    Location
    India
    Posts
    2

    Unanswered: MS SQL Server - LOCK Info

    Hi

    The system tables syslocks,syslockinfo give information on locked resources, spid, object_id,lock mode, lock status etc.

    How will i get object_name

    for example if a table is being locked above sys tables give object id, how can i get the actual table name ( object_name)

    Please let me know
    Best Regards
    THNQdigital

  2. #2
    Join Date
    Feb 2004
    Posts
    4

    Re: MS SQL Server - LOCK Info

    join with sysobjects table

  3. #3
    Join Date
    Feb 2004
    Location
    India
    Posts
    12

    Re: MS SQL Server - LOCK Info

    You can get it using object_name(object_id) in your select query.

    Thanks.
    Pat

    --------------------------
    Originally posted by fridays
    join with sysobjects table

  4. #4
    Join Date
    Feb 2004
    Location
    India
    Posts
    2

    Re: MS SQL Server - LOCK Info

    Originally posted by fridays
    join with sysobjects table



    Hi ,

    i found lock info for spid 12 using sp_lock 12

    got objid = 'numxxxxxx'
    type =TAB
    mode=Sch-M and Status = WAIT

    i tried joining syslockinfo with sysobjects like below

    select si.rsc_objid, si.req_spid , so.name from
    SYSLOCKINFO si ,SYSOBJECTS so where
    si.req_spid =12 and --si.rsc_type =5 and
    si.rsc_objid= 736846995 and so.xtype='U'

    This doesnot seem to gimme what i am looking for..

    My reqirement is what is Object_Name(objid given by splock spid)


    Please advise what am i doign wrong.
    Thanks
    THNQdigital

  5. #5
    Join Date
    Feb 2004
    Posts
    4

    Re: MS SQL Server - LOCK Info

    use master
    select a.name , b.rsc_objid , c.spid
    from sysdatabases a , syslockinfo b , syslocks c
    where c.spid = b.req_spid and a.dbid = c.dbid
    and c.spid = 'xxxx' and .....

    u will know database name 'yyyy' and objid 'zzzzz'
    then
    select name from yyyy.dbo.sysobjects where id = 'zzzzzz'




    Originally posted by THNQdigital
    Hi ,

    i found lock info for spid 12 using sp_lock 12

    got objid = 'numxxxxxx'
    type =TAB
    mode=Sch-M and Status = WAIT

    i tried joining syslockinfo with sysobjects like below

    select si.rsc_objid, si.req_spid , so.name from
    SYSLOCKINFO si ,SYSOBJECTS so where
    si.req_spid =12 and --si.rsc_type =5 and
    si.rsc_objid= 736846995 and so.xtype='U'

    This doesnot seem to gimme what i am looking for..

    My reqirement is what is Object_Name(objid given by splock spid)


    Please advise what am i doign wrong.
    Thanks
    THNQdigital

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •