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
Igor Micev,My blog: www.igormicev.com