Get XEvent Target Properties

Extended Event (XEvent) feature is available as public preview in Azure SQL Database as announced here. XEvent supports 3 types of targets – File Target (writes to Azure Blob Storage), Ring Buffer and Event Counter. Once we’ve created an event session, how do we inspect the event session target properties? This blog post describes how to do this in 2 ways: using the User Interface in SSMS and using T-SQL.

Using SSMS UI To Inspect XEvent Session Target Properties

In SSMS, connect to your Azure SQL DB server, e.g. mssqlgirl.database.windows.net. Please ensure that you are using the latest version of SSMS.

1. Navigate to the database where you have created the XEvent session, and expand Extended Events > Sessions.
2. Right click on the XEvent session and choose Properties.
   
3. Select Data Storage from the Session Properties window, and click on any of the target properties that you would like to inspect, as illustrated below.
   

   

An alternative to the above is to script the session out in SSMS to get the full definition of the XEvent session (instead of Step 2 above), i.e.

This method allows you to inspect XEvent session target properties one session at a time.

Using T-SQL To Inspect XEvent Session Target Properties

Run the following script on SSMS. This will provide you a list of properties that you have set when the XEvent sessions were created.

SELECT 
	DB_NAME()			AS [Database], 
	es.[name]			AS [Session],
	c.[object_name]		AS [SessionTarget],
	c.[column_name]		AS [PropertyName],
	c.[column_value]	AS [PropertyValue]
FROM    sys.dm_xe_database_session_object_columns c
	INNER JOIN sys.dm_xe_database_sessions es
		ON es.address = c.event_session_address 
WHERE c.[object_type] = 'target';

Below is an example of what the output looks like on my environment:

This method allows us to inspect XEvent session target properties one database at a time.

Wrap Up

After creating XEvent sessions, we may from time to time need to find out target properties of these sessions. There are 2 easy ways – using SSMS User Interface or programmatically using T-SQL.

Further Reading

• Using Extended Events on Azure SQL Database
• Extended Events in SQL Database