Locking..

  • HI

    All,

    How to find the object level Locks.Normally i am doing following steps.

    1)Sp_lock

    2)After getting the out put am finding the database name by using the dbid Hint.

    3)After getting the DBname am executing the following TSQL(There is Objid named column is there that mean Object ID !)

    Select * from sysobjects where id='888888'

    -------After this long process am getting the exact report about the locking .I mean which table is locked by which type of lock.

    Now my question is is there any sql statement to find the object level locking (report sholud show the object name and Locking type)

    Thanks and regards

    Ashwin vp

    Ashwin VP
    CSC India ...

  • with Sp_lock it will provide you the spid that has the lock.

    To get to see the last statement that spid is executing,

    select est.text

    FROM sys.dm_exec_sessions ES

    left join sys.dm_exec_requests ER

    on ER.session_id = ES.session_id

    OUTER Apply sys.dm_exec_sql_text(ER.sql_handle) EST

    where es.session_id = Thespid

    Johan

    Learn to play, play to learn !

    Dont drive faster than your guardian angel can fly ...
    but keeping both feet on the ground wont get you anywhere :w00t:

    - How to post Performance Problems
    - How to post data/code to get the best help[/url]

    - How to prevent a sore throat after hours of presenting ppt

    press F1 for solution, press shift+F1 for urgent solution 😀

    Need a bit of Powershell? How about this

    Who am I ? Sometimes this is me but most of the time this is me

  • I'd suggest using sys.dm_tran_locks instead of the old method. You can get much more granular information and combine the output with other views to get a great deal more information.

    "The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
    - Theodore Roosevelt

    Author of:
    SQL Server Execution Plans
    SQL Server Query Performance Tuning

Viewing 3 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply