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

试图创建 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.

相关文章