Aged Data Not Removed in SQL Server Temporal History Table

Question

ENVIRONMENT:
SQL Server 2017 Enterprise running on Windows Server 2016 Standard

Test Case:

1. 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"
2. Inserted 1 record in each table
3. Create a SQL Agent Job to modify the record in each table and let job run for 3 days
4. 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.
5. 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.

Answer 1

The retention policy is enforced only run once a day via the SQL Agent job "syspolicy_purge_history".

Answer 2

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 Policy

And 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