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

 

SQL Server – Extended Event – Data Storage – Change Folder / File Name

Background

Due to Storage Constraints having to change the folder that Extended Events are captured in.

 

GUI

I know it is not possible to do so via GUI.

As shown below, just as your author, the properties are dimmed.

 


Script

So let us script it

Outline

  1. Stop the Extended Event Session
  2. Drop the Extended Event Session target
    • In our case, we are using the adopted default target name of package0.event_file
  3. Add a new Target
    • Keeping everything from the old target
    • But, changing the targeted folder
  4. Restart the Extended Event Session

Code




	ALTER EVENT SESSION [performance]   
	ON SERVER  
	STATE = stop;  

	ALTER EVENT SESSION [performance]  
		ON SERVER 
		DROP TARGET package0.event_file;

	ALTER EVENT SESSION [performance]  
		ON SERVER 
		ADD TARGET package0.event_file
		(
			SET 
				  filename=N'C:\Microsoft\SQLServer\ExtendedEvents\TraceFiles\performance.xel'
				, max_file_size=(100)
				, max_rollover_files=(100)
		)

	ALTER EVENT SESSION [performance]   
	ON SERVER  
	STATE = start;  

GO