Not removing aged data per HISTORY_RETENTION_PERIOD policy

Viewed 354

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.

1 Answers

UPDATE: I had the wrong assumption regarding Historical table purge. I was expected the oldest data to be removed from table, but SQL Server does not guarantee order of data be be removed. When I check by count of record, I can see data being removed.

Related