Hi,

It's easy with extended events (XE) in sql server 2012 by help of its GUI. It should be possible with XE 2008 R2, but you'll have to write some code (easier if you can find a gui for sql 2008 r2).

Another option is to use some code in a scheduled job and collect data for a certain period.

I use the following code to catch locks:

SELECT

L1.resource_type ,

DB_NAME(L1.resource_database_id) AS DatabaseName ,

CASE L1.resource_type

WHEN 'OBJECT' THEN OBJECT_NAME(L1.resource_associated_entity_id,L1.resource_database_id)

WHEN 'DATABASE' THEN 'DATABASE'

ELSE CASE

WHEN L1.resource_database_id = DB_ID() THEN

(SELECT

OBJECT_NAME(object_id, L1.resource_database_id)

FROM sys.partitions

WHERE hobt_id = L1.resource_associated_entity_id

)

ELSE NULL

END

END AS ObjectName ,

L1.resource_description ,

L1.request_session_id ,

L1.request_mode ,

L1.request_status

FROM

sys.dm_tran_locks AS L1

JOIN sys.dm_tran_locks AS L2 ON L1.resource_associated_entity_id = L2.resource_associated_entity_id

WHERE

L1.request_status <> L2.request_status

AND ( L1.resource_description = L2.resource_description

OR ( L1.resource_description IS NULL AND L2.resource_description IS NULL)

)

ORDER BY

L1.resource_database_id ,

L1.resource_associated_entity_id ,

L1.request_status ASC;

Regards,

IgorMi