The retention policy is enforced only run once a day via the SQL Agent job "syspolicy_purge_history".
Aged Data Not Removed in SQL Server Temporal History Table
ENVIRONMENT:
SQL Server 2017 Enterprise running on Windows Server 2016 Standard
Test Case:
- Create 2 identical temporal tables (differ only by name) where 1 table has HISTORY_RETENTION_PERRIOD set to 1 day. The other table is set to "INFINITE"
- Inserted 1 record in each table
- Create a SQL Agent Job to modify the record in each table and let job run for 3 days
- On the 3rd day, verify table with retention period of 1 day has first day of data purged in associated history table and the 2nd history table still contain data from day one.
- Alter 2nd table to set retention period to 1 day
EXPECTED:
data will be purged from 2nd historical table in same manner as 1st temporal table.
ACTUAL:
Aged data in 2nd historical table is not removed per retention policy set.
Is this expected behavior or am I missing a step in purging data in the second temporal table.
1 additional answer
Sort by: Most helpful
-
m 4,271 Reputation points
2020-08-18T03:15:53.677+00:00 Hi @Ed Woo ,
Is this expected behavior or am I missing a step in purging data in the second temporal table.
What is your SQL SERVER VERSION? And how do you change the setting or t_sql to alter the retention policy? Follow steps as this: how-to-configure-retention-policy
And there are many approaches to managing historical data in the temporal history table are available:
• Stretch Database
• Table Partitioning
• Custom Cleanup Script
• Retention PolicyAnd note: Using the Temporal History Retention Policy approach applies to SQL Database and SQL Server 2017 starting from CTP 1.3.
More information: temporal-history-table-retention-in-sql-server-2017
BR,
Mia
If the reply helped, please do “Accept Answer” and upvote it.--Mia