How can I delete extended event files from within Managed Instance?

Question

Simplifying a few details, I have a recurring process on SQL Server 2019 which runs every 10 minutes and does the following:

• Reads data from extended event file targets using sys.fn_xe_file_target_read_file
• Processes the data and saves it to a table in SQL Server
• Stops and starts the event session to remove all data from the extended event file targets.

In this way, I can process all of the data that has accumulated since the last time it ran. I am trying to make this process work with the Managed Instance platform. Unfortunately, stopping and starting the event session does not clear out the xel files on the Managed Instance platform:

This is a problem because my recurring process will continue to process the same data over and over. It also means that the temporary xel data takes up way more storage than before.

Is there a way to delete an .xel file using T-SQL on the Managed Instance platform? xp_cmdshell is not supported. I even tried the undocumented sys.xp_delete_files procedure but that didn't seem to work either.

I could use Azure lifecycle management policies if I have no better option. However, as far as I can tell, it could delete files before they were processed if there was some kind of error in my T-SQL process. I'd prefer to explicitly remove the processed xel files as part of my automated system.

Answer 1

As a workaround, you may process data using the last timestamp already inserted in your SQL table by adding a condition in the WHERE clause. To minimize storage, you can set a lower maximum rollover file in the extended event.

Answer 2

There is currently no way to delete these files from Managed Instance. Instead, the recommended methodology is to delete these files directly using the Azure Storage APIs.

Even more efficient than that would be using a live event session, either through QueryableXEventData or XELite (XELite is easier to use since it works with .NET Core). This is probably the cheapest option in terms of performance short of reading files off of the server while giving you real-time data. Note that this is still subject to buffer processing delays (30s by default, but can be as low as 1s).