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

user_8526120 0
Viewed 1690

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.

sql-server c#
2 Answers

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
user_37111620

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();
            }
        }
user_41228720
Related