How to remove SQL Server Extended Events Event files (.xel)?

Question

We've found that over time a lot of .xel files are stored in sql server and it will unnecessary fill the space on the drive (we don't need them after a while). Also, the associated Extended Event Session might be not ended.

We have a cleanup job that is triggered every week and it cleans unused files.

Is there a way to delete these files via c# code or SQL ?

How to find the .xel (using c# or sql) files I am interested in (related to a certain session) ?

If I drop an SQL extended event session, will it clear its .xel logs ?

Any suggestions would be very appreciated.

Dropping the session will clear the log. Please read https://stackoverflow.com/questions/48830499/how-to-clear-sql-server-extended-events-event-file — EzLo, Jan 07 '19 at 10:15
Ohh ... actually I saw that question but I missed the part where it is saying that dropping the session will clear the logs too. Thank you !! — Adrian Chiritescu, Jan 07 '19 at 11:15
@AdrianChiritescu, dropping the xe session will not delete the xel files. If you are concerned about space, use rollover trace files and specify a limited size and number. — Dan Guzman, Jan 07 '19 at 11:23
@DanGuzman I thought that dropping the event session would clear those files too. I will have a look at your suggestion (rollover trace files) but can you please give me some other hints regarding what I am trying to accomplish ? I am trying to clear those files when they are older then one month using c# or SQL in a background cleanup job, is this possible ? Thank you ! — Adrian Chiritescu, Jan 07 '19 at 11:34

Dan Guzman · Answer 1 · 2019-01-07T12:18:23.147

2

Dropping the Extended Event session will not delete the file target file(s).

You can schedule a SQL Agent Job to remove old files via a PowerShell script according to your retention criteria. Be aware the account used to run the job will of course need delete permissions, which is not an issue if you run SQL Server Agent under the same account as SQL Server and specify the SQL Server Agent Account for Run as.

Below is PowerShell command example for a PowerShell job step type to delete files older than 30 days:

Get-ChildItem -Path "D:\SqlTraceFiles" -Filter "*.xel" | where {$_.lastwritetime -lt (get-date).adddays(-30)} | Remove-Item -force

edited Jan 07 '19 at 12:18

answered Jan 07 '19 at 11:45

Dan Guzman

43,250
3
46
71

Thank you very much for your help @Dan Guzman ! I hope you don't mind, but I am choosing the other answer as it was suggested a C# solution which is more convenient for our project but here are very good things that I've learned and I will keep them in mind in the future. Also, you've spotted I was on the wrong way, thinking that dropping the session would delete the .xel files which aren't actually. Thank you for your effort trying to help me ! – Adrian Chiritescu Jan 07 '19 at 21:23
@AdrianChiritescu, hust so you're aware, there is no need to stop the trace unless you want to delete the currently active file. – Dan Guzman Jan 07 '19 at 22:40

score 1 · Accepted Answer · answered Jan 07 '19 at 19:24

The following C# code will remove .xel files that are older than one month from the specified folder. I'm guessing you only want to look at the date, not time, that the file was created thus CreationTime.Date is compared to DateTime.Today, and any files older than one month are deleted. A reference to the System.IO namespace will be necessary. If the extended event is currently active it will be using the file and the file will not be able to be deleted. You can either handle this using try...catch blocks in your code or run a command to stop the event before deleting the files then turn the event back on afterwards. Taking the approach of starting and stopping each event will require knowing the name of the specific event to use as done below. If using this option, you can send these as SQL Commands from C# code or as another step in a SQL Agent job. The T-SQL example stops an extended event, and it can be re-enabled by changing the STATE to START instead.

Example Stop Event Statement:

ALTER EVENT SESSION YourExtendedEvent ON SERVER 
STATE = STOP

Delete old Extended Event Files:

        string directory = @"C:\YourFilePath\";
        DirectoryInfo di = new DirectoryInfo(directory);

        foreach (FileInfo fi in di.EnumerateFiles())
        {
            if (fi.Extension == ".xel" && fi.CreationTime.Date < DateTime.Today.AddMonths(-1))
            {
                fi.Delete();
            }
        }

This is exactly what I needed, thank you very much for your prompt answer ! — Adrian Chiritescu, Jan 07 '19 at 21:13

How to remove SQL Server Extended Events Event files (.xel)?

2 Answers2