Cleanup historical data in Temporal table using Retention Policy

Temporal tables were introduced by Microsoft in SQL Server 2016. From SQL Server 2016 onwards, temporal tables can be used to capture DML changes on a table without writing a single line of code. We have already discussed temporal tables in multiple blogs which can help us to understand it in more detail:

1. Temporal Table in SQL Server
2. Convert existing table to temporal table
3. Querying temporal table data
4. Audit created and modified by user in temporal table (without trigger)

In this post, we will discuss how we can enable retention policy on a temporal table which can help us to cleanup the older data from the history table of the temporal table. Removing the historical data from the history table is not an easy task. However, with the help of a configuration available with a temporal table named “HISTORY_RETENTION_PERIOD“, we can easily enable the cleanup process without writing any complex process.

Manage historical data in the temporal table using Retention Policy

We are going to demonstrate how we can enable an auto cleanup process for a temporal table. When we create a temporal table, a history table is also gets created to store all the changes being performed into the main table. However, with time, the history table becomes very large and the data inside the table becomes unusable. A retention policy can help us to remove the unused data from the history table automatically. In order to enable a retention policy on a temporal table at the time of the table creation, we can use the below script:

USE TestDB
GO
 
CREATE TABLE dbo.tbl_EmployeeMaster
(
    EmployeeId INT NOT NULL PRIMARY KEY,
    Name NVARCHAR(256) NOT NULL,
    EmpAddress VARCHAR(256) NOT NULL,
    Contact VARCHAR(20) NOT NULL,
    Designation VARCHAR(256),
    DateOfBirth DATETIME,
    Gender CHAR(1) NOT NULL,
    StartTime DATETIME2(0) GENERATED ALWAYS AS ROW START NOT NULL,
    EndTime DATETIME2(0) GENERATED ALWAYS AS ROW END NOT NULL,
    PERIOD FOR SYSTEM_TIME (StartTime, EndTime)
)
WITH 
(
	SYSTEM_VERSIONING = ON 
	(
		HISTORY_TABLE = dbo.tbl_EmployeeMaster_History,
		HISTORY_RETENTION_PERIOD = 6 MONTHS,
		DATA_CONSISTENCY_CHECK = ON
	)
)
GO

In the above code, we have created a temporal table named “dbo.tbl_EmployeeMaster” with a history table named “dbo.tbl_EmployeeMaster_History“. We have used “HISTORY_RETENTION_PERIOD = 6 MONTHS” in order to enable the cleanup process to remove the older data from the history table automatically once the data is older than 6 months. The “HISTORY_RETENTION_PERIOD” clause needs “TEMPORAL_HISTORY_RETENTION” setting to be enabled at the database label.

Note: By default, the “TEMPORAL_HISTORY_RETENTION” setting is enabled in SQL Server at the database level.

Check if retention policy is enabled on the database

If we want to check if this setting is enabled at the database level, we can use the below query.

SELECT name AS DBName, is_temporal_history_retention_enabled AS IsRetentionEnabled
FROM sys.databases

Enable retention policy on a database

In order to enable this setting, if it has been disabled for some reason, we can use the below command:

ALTER DATABASE TestDB
SET TEMPORAL_HISTORY_RETENTION ON

Alter retention period of an existing temporal table

In order to enable the retention policy on an existing temporal table or to modify the retention period on an existing temporal table, we can use below ALTER statement:

ALTER TABLE dbo.tbl_EmployeeMaster
SET (SYSTEM_VERSIONING = ON (HISTORY_RETENTION_PERIOD = 6 MONTHS));

Allowed retention period unit for a temporal table

We can use below units to define the time period for the retention policy for a temporal table:

1. DAYS
2. WEEKS
3. MONTHS
4. YEARS
5. INFINITE – The default one.

Extract all temporal table details with retention period and policy for a database

We can use below query to review the temporal tables with their retention policies at the database level:

SELECT 
SCHEMA_NAME(T1.schema_id) AS TemporalTableSchemaName,
T1.name as TemporalTableName, SCHEMA_NAME(T2.schema_id) AS HistoryTableSchemaName,
T2.name as HistoryTableName,T1.history_retention_period AS HistoryRetentionPeriod,
T1.history_retention_period_unit_desc AS PeriodUnit,
DB.is_temporal_history_retention_enabled AS IsRetentionPolicyEnabled
FROM sys.tables T1
OUTER APPLY (select is_temporal_history_retention_enabled from sys.databases
where name = DB_NAME()) AS DB
LEFT JOIN sys.tables T2
ON T1.history_table_id = T2.object_id WHERE T1.temporal_type = 2

Thanks for the reading. Please share your inputs in the comment section.