sys.dm_xe_objects 中缺少扩展事件阻止进程报告

2022-01-16 00:00:00 sql-server sql-server-2008-r2 extended-events

试图创建 SERVER EVENT SESSION 来捕获blocked_process_report &xml_deadlock_report 事件到一个文件中，以供以后使用以下语句进行分析；

Trying to create SERVER EVENT SESSION to capture blocked_process_report & xml_deadlock_report events to a file for later analysis with the following statement;

CREATE EVENT SESSION [blocked_process] ON SERVER
ADD EVENT sqlserver.blocked_process_report(
    ACTION(sqlserver.client_app_name,
           sqlserver.client_hostname,
           sqlserver.database_name)) ,
ADD EVENT sqlserver.xml_deadlock_report (
    ACTION(sqlserver.client_app_name,
           sqlserver.client_hostname,
           sqlserver.database_name))
ADD TARGET package0.asynchronous_file_target
(SET filename = N'c:	empXEventSessionslocked_process.xel',
     metadatafile = N'c:	empXEventSessionslocked_process.xem',
     max_file_size=(65536),
     max_rollover_files=5)
WITH (MAX_DISPATCH_LATENCY = 5SECONDS)

收到如下错误尝试执行该语句；

Receive the following error try to execute this statement;

消息 25623，第 16 级，状态 1，第 1 行事件名称sqlserver.blocked_process_report"无效，或找不到对象**

Msg 25623, Level 16, State 1, Line 1 The event name, "sqlserver.blocked_process_report", is invalid, or the object could not be found**

按sys.dm_xe_objects

从 sys.dm_xe_objects 中选择 *按名称排序；

SELECT * FROM sys.dm_xe_objects order by name;

从 binary_data 转到布尔值，没有找到blocked_process_report"，因此出现错误.

Goes from binary_data to boolean, no "blocked_process_report" found, hence the error.

当前版本:Microsoft SQL Server 2008 R2 (SP2) - 10.50.4000.0 (X64) 2012 年 6 月 28 日 08:36:30 版权所有 (c) Microsoft Corporation Express Edition (64-bit) o​​n Windows NT 6.1 (Build 7601:服务包 1)

Current version: Microsoft SQL Server 2008 R2 (SP2) - 10.50.4000.0 (X64) Jun 28 2012 08:36:30 Copyright (c) Microsoft Corporation Express Edition (64-bit) on Windows NT 6.1 (Build 7601: Service Pack 1)

我看到其他关于此失踪事件的问题的参考资料，但没有回复.

I see other references to questions regarding this missing event but no responses.

我做错了什么?

推荐答案

很遗憾，blocked_process_report 在 SQL Server 2012 中被添加为可跟踪的扩展事件.

Unfortunately, the blocked_process_report was added as a traceable extended event in SQL Server 2012.

由于直到 SQL Server 2012 才添加扩展事件向导，我将使用以下查询来查看可用的扩展事件:

Since the extended events wizard(s) weren't added until SQL Server 2012, I would use the following query to view the available extended events: 

SELECT *
FROM sys.dm_xe_objects AS Events
WHERE Events.object_type = 'event' AND Events.name LIKE '%blocked%'
ORDER BY Events.name;

或者更好地阅读 thisJonathan Kehayias 的文章非常好.

Or even better read this EXCEPTIONALLY good article by Jonathan Kehayias.

相关文章