0

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?

TheGameiswar
  • 27,855
  • 8
  • 56
  • 94
Kurt
  • 117
  • 1
  • 3
  • 12

2 Answers2

2

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.

S.Karras
  • 1,483
  • 15
  • 19
0

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)
ORDER BY 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.

Mirza Baig
  • 1
  • 1