extended events blocked process report missing fro

问题:

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:\temp\XEventSessions\blocked_process.xel',
     metadatafile = N'c:\temp\XEventSessions\blocked_process.xem',
     max_file_size=(65536),
     max_rollover_files=5)
WITH (MAX_DISPATCH_LATENCY = 5SECONDS)

Receive the following error try to execute this statement;

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

A query in name order of sys.dm_xe_objects

SELECT * FROM sys.dm_xe_objects order by name;

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

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.

What am I doing wrong?

回答1:

Unfortunately, the blocked_process_report was added as a traceable extended event in 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;

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