SQL Server – Extended Event – Metadata – Get Current Target File

Background

For some optimization work that we are doing, I will like to know the name of the Extended Events’ Engine currently targeted file.

 

Code

Guide

As always we know that Microsoft exposes a lot of metadata via Dynamic Management Views.

The specific ones that we will use are:

  1. sys.dm_xe_sessions
    • Currently executing Extended Event Sessions
  2. sys.dm_xe_session_targets
    • Targets for currently executing sessions

SQL



SELECT
		  [extendedEvent]
			= tblXES.[name]

		, [executionCount]
			= tblXEST.execution_count

		, [executionDuration]
			= tblXEST.execution_duration_ms

		, [targetData]
			= tblXEST.target_data

		, [fileCurrent]
			= cast(tblXEST.target_data as xml).value('(//File/@name)[1]','nvarchar(255)')

FROM sys.dm_xe_sessions AS tblXES

INNER JOIN sys.dm_xe_session_targets AS tblXEST

    ON tblXES.[address] = tblXEST.[event_session_address]

where 
	(

		( tblXEST.target_name = 'event_file' )

	)


Output

Sample Output

 

Leave a Reply

Please log in using one of these methods to post your comment:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s