Extended Events Blocked Process Report Missing From Sys.dm_xe_objects

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 SES

Solution 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%'ORDERBY Events.name;

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

Solution 2:

Spent good chunk of time on the same issue and then finally managed to fix it. This error can occur due to two reasons:

1. Trying to create an extended event session on a SQL Server version that does not support that event i.e extended event add_xact_outcome exists in SQL Server 2019 but not in SQL Server 2012 so trying to create extended with add_xact_outcome in SQL Server 2012 will return this error.

2. This is tricky one. There is column in sys.dm_xe_objects called capabilities_desc and extended event session can ONLY be created for non-private extended events. Trying to create an extended event session for a private event will return this error too.

So, we need to add another WHERE condition:

SELECT *
FROM sys.dm_xe_objects AS Events
WHERE 
Events.object_type = 'event' AND 
Events.name LIKE'%blocked%'AND
(Events.capabilities_desc <> 'private' OR Events.capabilities_desc is null)ORDERBY Events.name;

Yes, Events.capabilities_desc is null is special case to handle null values as these values are not handled under 'private'.

Importantly, if we try to create an extended event session via Wizard then list available of extended events does not show the private extended events for the same reason.

Private extended events in SQL Server 2019, note events starting with vdw_

Now if we search, say, starting with vdw_ and none of the event is shown

Searching extended events while creating extended event via wizard

Found the same on Microsoft documentation: sys.dm_xe_objects (Transact-SQL)

Private. The only object available for internal use, and that cannot be accessed via the CREATE/ALTER EVENT SESSION DDL. Audit events and targets fall into this category in addition to a small number of objects used internally.