1

How do we purge events older than a day in Extended Events in SQL Server 2012. Stopping and Starting the session is not helping. Target : asynchronous_file_target Trying to create a job which will perform this operation daily.

2
  • Add the XE target to your question (e.g. file target. etc).
    – Dan Guzman
    Nov 2 '19 at 15:51
  • Done. Target is an xml file.
    – Kaushik
    Nov 2 '19 at 16:27
1

The example PowerShell command below will delete trace file target rollover files that haven't been written to for more than 24 hours. Change the trace file pattern to match your actual trace file target and schedule in a SQL Agent job PowerShell step for the daily purge.

Get-ChildItem  "C:\SqlTraceFiles\YourTraceName*.xel" | Where-Object -Property LastWriteTime -lt (Get-Date).AddDays(-1) | Remove-Item -ErrorAction Ignore

This command will not delete the active trace file since it's in use (ignoring the error). If the event volume is too low to cause a rollover at least once per day, consider restarting the trace daily to avoid older events accumulating in the same file.

1
  • It's bloody amazing that even in the 3rd quarter of 2021 that Microsoft still hasn't created an EE command to do this easily.
    – Jeff Moden
    Oct 21 at 15:07

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.