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_PERIOD set to 1 day. The other table is set to INFINITE.
CODE:
CREATE TABLE dbo.TemporalPurgeTestNew
( TemporalPurgeTestNewId int IDENTITY(1,1)
, InsertedDate datetime2(0)
, ModifiedDate datetime2(0)
, UpdateNum int
, CONSTRAINT pk_TemporalPurgeTestNew
PRIMARY KEY (TemporalPurgeTestNewId)
, SysStartTime datetime2 GENERATED ALWAYS AS ROW START NOT NULL
, SysEndTime datetime2 GENERATED ALWAYS AS ROW END NOT NULL
, PERIOD FOR SYSTEM_TIME (SysStartTime,SysEndTime))
WITH (SYSTEM_VERSIONING = ON
( HISTORY_TABLE = dbo.TemporalPurgeTestNewHistory
, DATA_CONSISTENCY_CHECK = ON
, HISTORY_RETENTION_PERIOD = 1 DAY
));
GO
CREATE TABLE dbo.TemporalPurgeTestExistTbl
( TemporalPurgeTestNewId int IDENTITY(1,1)
, InsertedDate datetime2(0)
, ModifiedDate datetime2(0)
, UpdateNum int
, CONSTRAINT pk_TemporalPurgeTestExistTbl
PRIMARY KEY (TemporalPurgeTestNewId)
, SysStartTime datetime2 GENERATED ALWAYS AS ROW START NOT NULL
, SysEndTime datetime2 GENERATED ALWAYS AS ROW END NOT NULL
, PERIOD FOR SYSTEM_TIME (SysStartTime,SysEndTime))
WITH (SYSTEM_VERSIONING = ON
( HISTORY_TABLE = dbo.TemporalPurgeTestExistTblHistory
));
GO
Inserted 1 record in each table
Create a SQL Server 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
CODE:
ALTER TABLE dbo.TemporalPurgeTestExistTbl
SET (SYSTEM_VERSIONING = ON
( HISTORY_TABLE = dbo.TemporalPurgeTestExistTblHistory
, DATA_CONSISTENCY_CHECK = ON
, HISTORY_RETENTION_PERIOD = 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.