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.

  • 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

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.

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.