Temporal history table retention in SQL Server 2017

By: Ameena Lalani   |   Comments (1)   |   Related: > Temporal Tables

Problem

Temporal tables were first introduced in SQL Server 2016. It is a great feature for tracking DML changes over the life of the data row in a particular table. Temporal history tables are meant to keep historical data for a long period of time. These history tables tend to grow very large, very quickly depending on the DML activities on your system.

Consider an example that you have a 10 row temporal table. Say you update these 10 rows 10 times a day. In 10 days you will have (10x10x10) 1000 rows in the history table. That is how fast it will grow. So it becomes a problem very soon if you have a heavy data modification pattern in your application.

In this tip, we are going to talk about various solutions available to us today to overcome this problem.

Solution

There are few workarounds you can implement manually, but now in SQL Server 2017 you have an automated way of managing the growing size of temporal history table. Let’s review the workarounds first and then look at the new retention feature.

Pros and Cons of Each Solution

You can read more about these solutions here.

1. Stretch Database

Stretch database feature was also introduced in SQL Server 2016 and is now available in standard edition as well since SP1. Its main use case is to offload historical and on premise data to the cloud. You can enable this feature on your temporal history table and can define the function on the datetime column to move the data older than certain date, quietly in background, to your Azure database.

This solution requires for you to have the database in Azure and Azure account. Querying the historical data will incur higher cost than inserting the data and storing it in the cloud.

2. Table Partitioning

This feature was first introduced in SQL server 2005 and since then many updates have been made to make it a robust performance improvement feature. You partition the temporal history table based on a function on the date column.  For example, each partition contains one month of data. Index maintenance is a breeze with partitioning for very large tables. You can very easily switch the partition of oldest history data to an Archive database.

Table Partitioning requires initial setup and deep understanding of how partition switching works. How Merge and split function works. You need to write and maintain the code such as partition schema and functions. You may have to write a view to get details from all partitions. But once setup, it works beautifully. You can even automate the partition switching process using the SQL Server Agent Jobs by writing some clever code.

3. Custom Script

You can write your own script to delete the data from the history table in smaller chunks. It requires, turning system_versioning OFF. That means either you have to take an application down time to do this or do it in a transaction so no one can update\delete from the table while your deletion\retention script is running.

It becomes much more difficult, if you have several temporal tables in a database and have different script for each one of them. It is better to keep same deletion or retention logic and automate the process through a stored procedure.

Temporal History Retention Policy

As we saw above, there are various ways by which we can manage history table growth, but none of them is simple and totally automated. These solutions require administrative overhead. Table partitioning is only available in Enterprise Edition of SQL server. The Microsoft team realized that and added the History Retention Policy for temporal tables in SQL Server 2017.

Your first step is to determine data retention period. Then you have to decide where to store this data. And finally you will decide how you will store and access that data.

Let’s work on this example. Create a database and create a temporal table with history retention clause. We are only using 2 days for history retention here because this is a test scenario. You can also define months or years as the retention unit.

CREATE TABLE TestTemporal(
Id INT CONSTRAINT PK_ID PRIMARY KEY,
CustomerName VARCHAR(50),
StartDate DATETIME2 GENERATED ALWAYS AS ROW START NOT NULL, 
EndDate DATETIME2 GENERATED ALWAYS AS ROW END NOT NULL,
PERIOD FOR SYSTEM_TIME (StartDate, EndDate)
)
WITH (SYSTEM_VERSIONING = ON 
         (HISTORY_TABLE = dbo.TestTemporalHistory, 
            History_retention_period = 2 DAYS
          )
      ) 
GO

History retention information can be retrieved from sys.tables system table.

SELECT name, temporal_type_desc, history_retention_period, history_retention_period_unit 
FROM sys.tables
GO;

The history_retention_period_unit column above can have one of the following values:

• -1: INFINITE (If History_Retention_Period is not defined for a temporal table, it means the history is going to be kept forever.)
• 3: DAY
• 4: WEEK
• 5: MONTH
• 6: YEAR

In SQL Server 2017, Is_Temporal_Retention_Enabled flag is automatically set to ON at the database level for each database at the time of its creation.

You can verify it by running the following query.

SELECT is_temporal_history_retention_enabled, NAME FROM sys.databases
GO;

If you turn off the retention flag at the database level, all the temporal tables, if they have retention clause defined, will drop the retention clauses. You can use the following command to turn on and off the history retention at the database level. But once you turn it back on, you have to redefine the History_Retention_Period at the table level.

ALTER DATABASE TemporalDB SET temporal_history_retention OFF
GO;
ALTER DATABASE TemporalDB SET temporal_history_retention ON
GO;
ALTER TABLE TestTemporal SET (SYSTEM_VERSIONING = ON (History_Retention_Period = 2 MONTHs));
GO;

Important thing to note here is that setting SYSTEM_VERSIONING to OFF does not preserve the retention period value either. Setting SYSTEM_VERSIONING ON without History_Retention_Period specified explicitly results in the Infinite retention period.

History Table Cleanup

One more advantage of using temporal history table is that it allows you to easily manage different aging periods for different temporal tables. Each is automatically managed by the system. When is_temporal_history_retention_flag is enabled at the database level, the system periodically checks for the aged records. Once matching rows are identified, the system automagically deletes them in the background, using an efficient delete mechanism, and the process is completely transparent to the application.

In our example, the ENDDATE column represents the end of SYSTEM_TIME period. The Cleanup process will validate the following condition in determining which rows to delete.

ENDDATE < DATEADD (Days, -2, SYSUTCDATETIME ())
GO;

One additional requirement that you should be aware of is that the history table should have a clustered index defined on the column that will be used in the history cleanup. SQL Server automatically creates this clustered index when you use the syntax of creating a temporal table that I showed you in the beginning of this tip. If you add the temporal history table after creating the temporal table, you have to manually create that clustered index.

Another advantage of using the above syntax to create temporal table is that it will also enable page level compression for the history table. This feature further optimizes the storage of temporal history table.

The following error will occur if clustered index is not defined on the history table and you try to define the retention period.

The following error will occur if retention period is defined on a temporal table and you try to drop the clustered index on the history table.

These are very descriptive error messages and tells you exactly what to do.

Summary

Temporal Table are a great feature introduced in SQL Server 2016, but it is missing a history retention piece. SQL Server 2017 delivered that request from the users. There are still room for improvements in this feature and hopefully we will see those in upcoming SQL Server releases. I also like you to think about if your business has a requirement to never delete any historical data, what will you do? Please write your answers in the comment section. Maybe down the road, I will write another post on that topic.

Next Steps

• As you upgrade to SQL server 2017 and planning to use temporal table feature for automatically retaining and cleaning historical data, keep this tip in mind.
• Check out other related tips here and here.
• Read more MSSQLTips.com articles about temporal table.

About the author

Ameena Lalani is a MCSA on SQL Server 2016. She is a SQL Server veteran and started her journey with SQL Server 2000. She has implemented numerous High Availability and Disaster Recovery solutions.

This author pledges the content of this article is based on professional experience and not AI generated.

View all my tips