July 22, 2009 at 3:33 am
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 ...
July 22, 2009 at 5:52 am
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
July 22, 2009 at 6:08 am
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 RooseveltThe Scary DBAAuthor of: SQL Server 2017 Query Performance Tuning, 5th Edition and SQL Server Execution Plans, 3rd EditionProduct Evangelist for Red Gate Software
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply