[BUG] Fix #951 — clean up Monitor_LongQueries_*.trc files in data_retention not working and job failing

[CADelete]

Component

Full Dashboard

Performance Monitor Version

2.11.0

SQL Server Version

SQL Server 2019

Windows Version

Windows Server 2019

Describe the Bug

Data Retention job failing on new version. Output from job log

Found 7 Monitor LongQueries_* file(s) in I:\Logs\MSSQLSERVER; cutoff=2026-05-11T07:38.22.239 [SQLSTATE 010000]
Msg 22049, Sev16, Sate 1: xp_delete_files() returned error 2, 'The system cannot find the file specified.' [SQLSTATE 42000]

Steps to Reproduce

Happening on all instances following upgrade to 2.11

Expected Behavior

Should complete and remove data

Actual Behavior

Job fails instantly

Error Messages / Log Output

Found 7 Monitor LongQueries_* file(s) in I:\Logs\MSSQLSERVER\; cutoff=2026-05-11T07:38.22.239 [SQLSTATE 010000]
Msg 22049, Sev16, Sate 1: xp_delete_files() returned error 2, 'The system cannot find the file specified.' [SQLSTATE 42000]

Screenshots

Additional Context

Worked in previous version

[CADelete]

The 7 files from the message are not within the cut off period, error message suggests that it cannot find the file which would be correct as there are no files to delete

This also matches the code, however it still fails

[erikdarlingdata]

Thanks for the detailed report — and for the follow-up screenshots, they pointed straight at the cause.

Root cause

The trace-file cleanup added in v2.11.0 was broken in two ways:

1. It called xp_delete_file with a wildcard path (...\Monitor_LongQueries_*.trc). That argument is a folder path, not a wildcard — so xp_delete_file looked for a folder by that literal name, didn't find it, and raised Msg 22049, error 2 ("cannot find the file specified"). That error comes from an extended stored procedure and is not catchable by TRY...CATCH, so it failed the whole Data Retention job step — exactly what you saw.
2. More fundamentally, xp_delete_file cannot delete .trc files at all. It only deletes SQL Server backup files and Maintenance Plan report files, and validates the file header. So that cleanup step could never have worked regardless of the path bug.

Fix

PR #973 (merged to dev):

• Removed the broken xp_delete_file cleanup from config.data_retention — that stops the job failure.
• collect.trace_management_collector now creates the long-query trace with a rollover file-count cap (sp_trace_create @filecount), so SQL Server prunes old .trc files itself. The scheduled collector also stopped tearing the trace down and recreating it every cycle, which is what generated the pile-up of files in the first place.
• Added tools/Remove-OrphanedTraceFiles.ps1 — a one-time script to sweep the .trc files already on disk.

This will ship in the next release.

In the meantime

The Monitor_LongQueries_*.trc files in I:\Logs\MSSQLSERVER\ are safe to delete — they're closed trace files (only the currently-running trace's file is in use, and that one is skipped). You can delete the old ones manually now if you'd like; the Data Retention job will keep failing on the trace-cleanup step until you pick up the next release.