[BUG] Data Retention Schedule Not Applied / Job Uses Fixed Value (30 Days) #237
Description
Component
Full Dashboard
Performance Monitor Version
1.2.0
SQL Server Version
SQL Server 2019 CU 32
Windows Version
Windows Server 2019 Standard
Describe the Bug
It appears that the PerformanceMonitor – Data Retention job may not be honoring the retention periods configured in the dashboard under Edit Schedules.
Following a discussion with Erik on the topic "Large and Rapid Database Growth + Clarification on Expected Storage Usage", I reduced the retention for Query Snapshots, Query Stats and Query Store to 3 days, expecting the system to delete older data accordingly.
However, no data was removed.
Upon further inspection, the PerformanceMonitor – Data Retention job seems to always execute:
EXECUTE config.data_retention @retention_days = 30, @debug = 1;
I also reviewed the config.data_retention procedure and it appears to use only the @retention_days parameter.
I did not find any reference to [config].[collection_schedule], where the per‑collector retention settings are stored.
This analysis is based on reverse engineering the code, so I cannot say with certainty whether this is intentional or a bug, but the behavior does not seem aligned with the UI configuration.
Steps to Reproduce
- Go to Dashboard → Edit Schedules.
- Set retention for Query Snapshots, Query Stats, Query Store to a lower value (e.g., 3 days).
- Wait for the scheduled retention job to run, or run it manually.
- Observe that:
- Data older than 3 days remains in the database.
- No cleanup occurs.
Expected Behavior
- The data retention job should:
Read the collector‑specific retention settings from the configuration stored in [config].[collection_schedule], or
Use the retention periods defined via the UI (Edit Schedules).
- Data older than the configured retention window (e.g., 3 days) should be deleted automatically.
- The SQL Agent job should not enforce a hardcoded value if dynamic retention is expected.
Actual Behavior
- Retention periods configured in the dashboard (set to 3 days) are not applied.
- The system does not delete data older than the configured retention.
- The SQL Agent job always passes a fixed value (@retention_days = 30) to the stored procedure.
- The config.data_retention procedure appears to use only this parameter value and does not read retention values from [config].[collection_schedule].
Error Messages / Log Output
Starting data retention: keeping data newer than 2026-01-24 07:39:22
Tables to clean:
Cleaning [collect].[blocked_process_xml] (keeping data after 2026-01-24 07:39:22)
Cleaning [collect].[blocking_BlockedProcessReport] (keeping data after 2026-01-24 07:39:22)
Cleaning [collect].[blocking_deadlock_stats] (keeping data after 2026-01-24 07:39:22)
Cleaning [collect].[cpu_scheduler_stats] (keeping data after 2026-01-24 07:39:22)
Cleaning [collect].[cpu_utilization_stats] (keeping data after 2026-01-24 07:39:22)
Cleaning [collect].[deadlock_xml] (keeping data after 2026-01-24 07:39:22)
Cleaning [collect].[deadlocks] (keeping data after 2026-01-24 07:39:22)
Cleaning [collect].[default_trace_events] (keeping data after 2026-01-24 07:39:22)
Cleaning [collect].[file_io_stats] (keeping data after 2026-01-24 07:39:22)
Cleaning [collect].[HealthParser_Blocking] (keeping data after 2026-01-24 07:39:22)
Cleaning [collect].[HealthParser_CPUTasks] (keeping data after 2026-01-24 07:39:22)
Cleaning [collect].[HealthParser_Deadlocks] (keeping data after 2026-01-24 07:39:22)
Cleaning [collect].[HealthParser_IOIssues] (keeping data after 2026-01-24 07:39:22)
Cleaning [collect].[HealthParser_MemoryBroker] (keeping data after 2026-01-24 07:39:22)
Cleaning [collect].[HealthParser_MemoryConditions] (keeping data after 2026-01-24 07:39:22)
Cleaning [collect].[HealthParser_MemoryNodeOOM] (keeping data after 2026-01-24 07:39:22)
Cleaning [collect].[HealthParser_PendingTasks] (keeping data after 2026-01-24 07:39:22)
Cleaning [collect].[HealthParser_SchedulerIssues] (keeping data after 2026-01-24 07:39:22)
Cleaning [collect].[HealthParser_SevereErrors] (keeping data after 2026-01-24 07:39:22)
Cleaning [collect].[HealthParser_SignificantWaits] (keeping data after 2026-01-24 07:39:22)
Cleaning [collect].[HealthParser_SystemHealth] (keeping data after 2026-01-24 07:39:22)
Cleaning [collect].[HealthParser_WaitsByCount] (keeping data after 2026-01-24 07:39:22)
Cleaning [collect].[HealthParser_WaitsByDuration] (keeping data after 2026-01-24 07:39:22)
Cleaning [collect].[latch_stats] (keeping data after 2026-01-24 07:39:22)
Cleaning [collect].[memory_clerks_stats] (keeping data after 2026-01-24 07:39:22)
Cleaning [collect].[memory_grant_stats] (keeping data after 2026-01-24 07:39:22)
Cleaning [collect].[memory_pressure_events] (keeping data after 2026-01-24 07:39:22)
Cleaning [collect].[memory_stats] (keeping data after 2026-01-24 07:39:22)
Cleaning [collect].[perfmon_stats] (keeping data after 2026-01-24 07:39:22)
Cleaning [collect].[plan_cache_stats] (keeping data after 2026-01-24 07:39:22)
Cleaning [collect].[procedure_stats] (keeping data after 2026-01-24 07:39:22)
Cleaning [collect].[query_snapshots_20260218] (keeping data after 2026-01-24 07:39:22)
Cleaning [collect].[query_snapshots_20260219] (keeping data after 2026-01-24 07:39:22)
Cleaning [collect].[query_snapshots_20260220] (keeping data after 2026-01-24 07:39:22)
Cleaning [collect].[query_snapshots_20260221] (keeping data after 2026-01-24 07:39:22)
Cleaning [collect].[query_snapshots_20260222] (keeping data after 2026-01-24 07:39:22)
Cleaning [collect].[query_snapshots_20260223] (keeping data after 2026-01-24 07:39:22)
Cleaning [collect].[query_stats] (keeping data after 2026-01-24 07:39:22)
Cleaning [collect].[query_store_data] (keeping data after 2026-01-24 07:39:22)
Cleaning [collect].[running_jobs] (keeping data after 2026-01-24 07:39:22)
Cleaning [collect].[session_stats] (keeping data after 2026-01-24 07:39:22)
Cleaning [collect].[session_wait_stats] (keeping data after 2026-01-24 07:39:22)
Cleaning [collect].[spinlock_stats] (keeping data after 2026-01-24 07:39:22)
Cleaning [collect].[tempdb_stats] (keeping data after 2026-01-24 07:39:22)
Cleaning [collect].[trace_analysis] (keeping data after 2026-01-24 07:39:22)
Cleaning [collect].[wait_stats] (keeping data after 2026-01-24 07:39:22)
Cleaning [collect].[waiting_tasks] (keeping data after 2026-01-24 07:39:22)
Cleaning [config].[collection_log] (keeping data after 2025-12-25 07:39:22)
Screenshots
No response
Additional Context
This conclusion is based on reverse‑engineering the job and stored procedures.
I may have misunderstood the intended design, so please treat this as an observation rather than a definitive bug.
If the retention is intentionally centralized at 30 days, then the UI schedules may not apply to cleanup, but if the UI settings are supposed to control retention, this may indicate a missing link between the job, the procedure, and the configuration tables.