我正在使用sql Server中的扩展事件捕获locks_lock_timeouts_greater_than_0事件.活动会议如下：
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)
如何确定锁定的对象以及当前锁定的对象？
更新：我需要这个用于sql Server 2008.如何在2012年之前的版本上调试锁定超时？
解决方法
看起来您想要的信息不会被此事件捕获.
您可以通过以下方式查看可用于事件的字段：
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
结果是：
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
但是,在sql 2012中,此事件由lock_timeout_greater_than_0替换(http://msdn.microsoft.com/en-us/library/ms144262.aspx),其中包含以下字段集
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
从这里我能够从他们的ID导出数据库(database_id)和我的案例表(各种各样在object_id,associated_object_id,resource_0).
我没有看到一种明显的方法来查找谁将此对象从此事件捕获的数据中锁定.
编辑 – 有关使用sp_lock和sp_who2调试锁定超时事件原因的示例,请参阅SQL Server Lock Timeout Exceeded Deleting Records in a Loop.
