I am capturing locks_lock_timeouts_greater_than_0
events using Extended Events in SQL Server. The event session is following:
CREATE EVENT SESSION MyQuery ON SERVER
ADD EVENT sqlserver.locks_lock_timeouts_greater_than_0
(
ACTION (sqlserver.sql_text, sqlserver.tsql_stack)
)
ADD TARGET package0.ring_buffer (SET max_memory = 4096)
WITH (max_dispatch_latency = 1 seconds)
How can I determine what object is locked and who is currently locking it?
UPDATE: I need this for SQL Server 2008. How to debug lock timeouts on pre-2012 versions?
It looks like the information you want is not captured by this event.
You can see what fields are available for an event with:
select p.name package_name, o.name event_name, c.name event_field, c.type_name field_type, c.column_type column_type
from sys.dm_xe_objects o
join sys.dm_xe_packages p
on o.package_guid = p.guid
join sys.dm_xe_object_columns c
on o.name = c.object_name
where o.object_type = 'event'
AND o.name = 'locks_lock_timeouts_greater_than_0'
order by package_name, event_name
the result is:
sqlserver locks_lock_timeouts_greater_than_0 ID uint16 readonly
sqlserver locks_lock_timeouts_greater_than_0 UUID guid_ptr readonly
sqlserver locks_lock_timeouts_greater_than_0 VERSION uint8 readonly
sqlserver locks_lock_timeouts_greater_than_0 CHANNEL etw_channel readonly
sqlserver locks_lock_timeouts_greater_than_0 KEYWORD keyword_map readonly
sqlserver locks_lock_timeouts_greater_than_0 count uint64 data
sqlserver locks_lock_timeouts_greater_than_0 lock_type uint64 data
However, in SQL 2012 this event was replaced ( http://msdn.microsoft.com/en-us/library/ms144262.aspx ) by lock_timeout_greater_than_0 which has the following set of fields
sqlserver lock_timeout_greater_than_0 UUID guid_ptr readonly
sqlserver lock_timeout_greater_than_0 VERSION uint8 readonly
sqlserver lock_timeout_greater_than_0 CHANNEL etw_channel readonly
sqlserver lock_timeout_greater_than_0 KEYWORD keyword_map readonly
sqlserver lock_timeout_greater_than_0 collect_resource_description boolean customizable
sqlserver lock_timeout_greater_than_0 collect_database_name boolean customizable
sqlserver lock_timeout_greater_than_0 resource_type lock_resource_type data
sqlserver lock_timeout_greater_than_0 mode lock_mode data
sqlserver lock_timeout_greater_than_0 owner_type lock_owner_type data
sqlserver lock_timeout_greater_than_0 transaction_id int64 data
sqlserver lock_timeout_greater_than_0 database_id uint32 data
sqlserver lock_timeout_greater_than_0 lockspace_workspace_id ptr data
sqlserver lock_timeout_greater_than_0 lockspace_sub_id uint32 data
sqlserver lock_timeout_greater_than_0 lockspace_nest_id uint32 data
sqlserver lock_timeout_greater_than_0 resource_0 uint32 data
sqlserver lock_timeout_greater_than_0 resource_1 uint32 data
sqlserver lock_timeout_greater_than_0 resource_2 uint32 data
sqlserver lock_timeout_greater_than_0 object_id int32 data
sqlserver lock_timeout_greater_than_0 associated_object_id uint64 data
sqlserver lock_timeout_greater_than_0 duration uint64 data
sqlserver lock_timeout_greater_than_0 resource_description unicode_string data
sqlserver lock_timeout_greater_than_0 database_name unicode_string data
From this I was able to derive the database (database_id) and in my case table (variously in object_id, associated_object_id, resource_0) from their Ids.
I didn't see an obvious way to find who was locking the object from the data captured by this event.
EDIT - see SQL Server Lock Timeout Exceeded Deleting Records in a Loop for an example of using sp_lock and sp_who2 to debug the cause of a lock timeout event.
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With