Logo Questions Linux Laravel Mysql Ubuntu Git Menu
HTML CSS JAVASCRIPT SQL PYTHON PHP BOOTSTRAP JAVA JQUERY R React Kotlin
 

MS SQL Server Temporal History Retention Cleanup Interval

Tags:

sql-server

The situation:

I have been trying to get a temporal table to auto-cleanup on a 1 day history retention period (for testing purposes). I believe I have met all of Microsoft's criteria for this to work, in accordance with MS's documentation,

Identification of matching rows and their removal from the history table occur transparently, in the background task that is scheduled and run by the system.

As you can see, under Query 2: Results and Query 3: Results, the history table is retaining data well past the 1 day mark. The temporal query is appropriately hiding the records which are outside the retention window, so I am making the assumption that SQL Server is recognizing the history retention period.

The questions:

  1. Have I missed a critical component, causing the history table to not be cleared?

    A. The temporal table is cleaning out.

  2. Is this mysterious "background task" not a daily occurrence / does anyone know the frequency of this task? I have yet to find this piece of information via Bing, Google, and StackOverflow searches.

    A. Does not appear to be a daily event. I have setup a test environment to deduce the schedule (see below for updates).

╒═════════╗     
│ Query 1 ║     
├─────────╜
│ --DB and Table retention configurations 
│ 
│ SELECT DB_NAME() as DatabaseName, DB.is_temporal_history_retention_enabled as TemporalHistoryRetentionEnabled 
│   , concat(SCHEMA_NAME(T1.schema_id), '.', T1.name) as TemporalTableName 
│   , concat(SCHEMA_NAME(T2.schema_id), '.', T2.name) as HistoryTableName 
│   , concat(T1.history_retention_period, ' ' , T1.history_retention_period_unit_desc) as HistoryRetentionPeriod 
│ 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 
└

╒══════════════════╗        
│ Query 1: Results ║        
├──────────────────╜
│ DatabaseName  TemporalHistoryRetentionEnabled  TemporalTableName  HistoryTableName           HistoryRetentionPeriod 
│ ------------  -------------------------------  -----------------  -------------------------  ---------------------- 
│ Sandbox       1                                dbo.TemporalTest3  dbo.TemporalTest3_History  1 DAY 
└


╒═════════╗     
│ Query 2 ║     
├─────────╜
│ --Temporal and Manual *all* records in table and history 
│ --Agent runs job every 30 minutes, writes to TemporalCleanupTest 
│ 
│ select 'Temporal' as [Action], * 
│ from TemporalTest3 
│ for system_time all 
│ union 
│ select 'Manual' as [Action], * 
│ from TemporalTest3 
│ union 
│ select 'Manual' as [Action], * 
│ from TemporalTest3_History 
│ order by Action desc, ValidTo desc 
└

╒══════════════════╗        
│ Query 2: Results ║        
├──────────────────╜
│ Action    col1    col2  ValidFrom                    ValidTo 
│ --------  ------  ----  ---------------------------  --------------------------- 
│ Temporal  ABC123  3     2019-12-04 22:50:28.4229184  9999-12-31 23:59:59.9999999 
│ Manual    ABC123  3     2019-12-04 22:50:28.4229184  9999-12-31 23:59:59.9999999 
│ Manual    ABC123  3     2019-12-04 22:50:22.7708507  2019-12-04 22:50:28.4229184 
│ Manual    ABC123  3     2019-12-04 22:25:39.5188391  2019-12-04 22:50:22.7708507 
│ Manual    ABC123  2     2019-12-04 22:25:35.9240760  2019-12-04 22:25:39.5188391 
│ Manual    ABC123  1     2019-12-04 22:25:31.6265788  2019-12-04 22:25:35.9240760 
│ Manual    ABC123  NULL  2019-12-04 22:25:22.4114106  2019-12-04 22:25:31.6265788 
└


╒═════════╗     
│ Query 3 ║     
├─────────╜
│ --TemporalCleanupTest: record count by TimeStamp 
│ 
│ select TimeStamp, Method, count(*)
│ from TemporalCleanupTest
│ where TimeStamp = (select max(TimeStamp) from TemporalCleanupTest)
│ group by TimeStamp, Method
│ order by TimeStamp desc, Method desc
└

╒══════════════════╗        
│ Query 3: Results ║        
├──────────────────╜
│ TimeStamp            Method    RecordCount
│ -------------------  --------  -----------
│ 2019-12-06 11:30:00  Temporal  1
│ 2019-12-06 11:30:00  Manual    6
└


╒═════════════════╗
│ Required Index: ║
├─────────────────╜
│ /*
│ Object:  Index [ix_TemporalTest3_History]
│ The cleanup task for tables with rowstore clustered index requires index to start with
│  the column corresponding the end of SYSTEM_TIME period. If such index doesn't exist, 
│  you cannot configure a finite retention period
│ */
│ CREATE CLUSTERED INDEX [ix_TemporalTest3_History] ON [dbo].[TemporalTest3_History]
│ (
│   [ValidTo] ASC, --column corresponding the end of SYSTEM_TIME period
│   [ValidFrom] ASC
│ )
│ WITH
│ (
│ PAD_INDEX = OFF
│ , STATISTICS_NORECOMPUTE = OFF
│ , SORT_IN_TEMPDB = OFF
│ , DROP_EXISTING = OFF
│ , ONLINE = OFF
│ , ALLOW_ROW_LOCKS = ON
│ , ALLOW_PAGE_LOCKS = ON
│ ) ON [PRIMARY]
└

Update 2019-12-10 15:23 UTC

Can you share the DDL that you used to create your table (I'm interested in the temporal parameters)? – Ben Thul

create table [Sandbox].[dbo].[TemporalTest3]
(
    [col1] nvarchar(100)
    , constraint [PK_TemporalTest3_col1] primary key clustered ([col1] ASC)
    , [col2] nvarchar(100) NULL
    , [ValidFrom] datetime2(7) generated always as row start
    , [ValidTo] datetime2(7) generated always as row end
    , period for system_time ([ValidFrom], [ValidTo])
)
with
(
    system_versioning = on
    (
        history_table = [dbo].[TemporalTest3_History]
        , history_retention_period = 1 days
    )
)

Update 2019-12-10 15:41 UTC The temporal table is cleaning out, however my hourly capture was kindly interrupted by the IT Staff... so I do not know when exactly the cleanout would have occurred. I am now left with the following questions:

  1. What exactly is the background task's schedule?
  2. Does it fire on service/server restart and/or missed scheduled time?

I will setup this test again, with another hourly capture, and provide an update with my findings. Surely I am not the only one with these data retention/cleanout questions. My capture log where the cleanout was recorded:

┌─────────────────────┬──────────┬────────┬──────┬─────────────────────────┬─────────────────────────┐
│ TimeStamp           │ Method   │ col1   │ col2 │ ValidFrom               │ ValidTo                 │
├─────────────────────┼──────────┼────────┼──────┼─────────────────────────┼─────────────────────────┤
│ 2019-12-07 06:00:00 │ Temporal │ ABC123 │ 3    │ 2019-12-04 22:50:28.423 │ 9999-12-31 23:59:59.999 │
│ 2019-12-07 06:00:00 │ Manual   │ ABC123 │ 3    │ 2019-12-04 22:50:28.423 │ 9999-12-31 23:59:59.999 │
│ ******************* │ ******** │ ****** │ **** │ *********************** │ *********************** │
│ 2019-12-06 17:00:00 │ Temporal │ ABC123 │ 3    │ 2019-12-04 22:50:28.423 │ 9999-12-31 23:59:59.999 │
│ 2019-12-06 17:00:00 │ Manual   │ ABC123 │ 3    │ 2019-12-04 22:50:28.423 │ 9999-12-31 23:59:59.999 │
│ 2019-12-06 17:00:00 │ Manual   │ ABC123 │ 3    │ 2019-12-04 22:50:22.771 │ 2019-12-04 22:50:28.423 │
│ 2019-12-06 17:00:00 │ Manual   │ ABC123 │ 3    │ 2019-12-04 22:25:39.519 │ 2019-12-04 22:50:22.771 │
│ 2019-12-06 17:00:00 │ Manual   │ ABC123 │ 2    │ 2019-12-04 22:25:35.924 │ 2019-12-04 22:25:39.519 │
│ 2019-12-06 17:00:00 │ Manual   │ ABC123 │ 1    │ 2019-12-04 22:25:31.627 │ 2019-12-04 22:25:35.924 │
│ 2019-12-06 17:00:00 │ Manual   │ ABC123 │ NULL │ 2019-12-04 22:25:22.411 │ 2019-12-04 22:25:31.627 │
└─────────────────────┴──────────┴────────┴──────┴─────────────────────────┴─────────────────────────┘
like image 365
Bryan__T Avatar asked Sep 17 '25 20:09

Bryan__T

1 Answers

The background task is indeed firing off on some kind of loose "Microsoft quality schedule". Most cleanout events occurred around the start of the day (00:00) and mid-day (12:00), though some occurred at other hours. I do not know why it took time to start cleaning out the temporal history, but it is working. I have extracted the data, where changes occurred, from my hourly log into the table below. Enjoy!

If there is any further data desired, please let me know and I will supply what I can.

┌─────────────────────┬──────────┬──────┐
│ TimeStamp           │ Method   │ Rows │
├─────────────────────┼──────────┼──────┤
│ 2019-12-11 11:00:00 │ Temporal │ 3    │
│ 2019-12-11 11:00:00 │ Manual   │ 3    │
├─────────────────────┼──────────┼──────┤
│ 2019-12-11 12:00:00 │ Temporal │ 4 +  │
│ 2019-12-11 12:00:00 │ Manual   │ 4 +  │
├─────────────────────┼──────────┼──────┤
│ 2019-12-11 13:00:00 │ Temporal │ 3 ─  │
│ 2019-12-11 13:00:00 │ Manual   │ 4 .  │
├─────────────────────┼──────────┼──────┤
│ 2019-12-11 14:00:00 │ Temporal │ 3 .  │
│ 2019-12-11 14:00:00 │ Manual   │ 3 ─  │
├─────────────────────┼──────────┼──────┤
│ 2019-12-12 00:00:00 │ Temporal │ 3 .  │
│ 2019-12-12 00:00:00 │ Manual   │ 4 +  │
├─────────────────────┼──────────┼──────┤
│ 2019-12-12 06:00:00 │ Temporal │ 3 .  │
│ 2019-12-12 06:00:00 │ Manual   │ 3 ─  │
├─────────────────────┼──────────┼──────┤
│ 2019-12-12 12:00:00 │ Temporal │ 4 +  │
│ 2019-12-12 12:00:00 │ Manual   │ 4 +  │
├─────────────────────┼──────────┼──────┤
│ 2019-12-12 13:00:00 │ Temporal │ 3 ─  │
│ 2019-12-12 13:00:00 │ Manual   │ 4 .  │
├─────────────────────┼──────────┼──────┤
│ 2019-12-12 14:00:00 │ Temporal │ 3 .  │
│ 2019-12-12 14:00:00 │ Manual   │ 3 ─  │
├─────────────────────┼──────────┼──────┤
│ 2019-12-13 00:00:00 │ Temporal │ 4 +  │
│ 2019-12-13 00:00:00 │ Manual   │ 4 +  │
├─────────────────────┼──────────┼──────┤
│ 2019-12-13 01:00:00 │ Temporal │ 3 ─  │
│ 2019-12-13 01:00:00 │ Manual   │ 4 .  │
├─────────────────────┼──────────┼──────┤
│ 2019-12-13 06:00:00 │ Temporal │ 3 .  │
│ 2019-12-13 06:00:00 │ Manual   │ 3 ─  │
├─────────────────────┼──────────┼──────┤
│ 2019-12-13 12:00:00 │ Temporal │ 3 .  │
│ 2019-12-13 12:00:00 │ Manual   │ 4 +  │
├─────────────────────┼──────────┼──────┤
│ 2019-12-14 00:00:00 │ Temporal │ 3 .  │
│ 2019-12-14 00:00:00 │ Manual   │ 5 +  │
├─────────────────────┼──────────┼──────┤
│ 2019-12-14 12:00:00 │ Temporal │ 3 .  │
│ 2019-12-14 12:00:00 │ Manual   │ 6 +  │
├─────────────────────┼──────────┼──────┤
│ 2019-12-14 17:00:00 │ Temporal │ 3 ─  │
│ 2019-12-14 17:00:00 │ Manual   │ 3 ─  │
├─────────────────────┼──────────┼──────┤
│ 2019-12-15 00:00:00 │ Temporal │ 4 +  │
│ 2019-12-15 00:00:00 │ Manual   │ 4 +  │
├─────────────────────┼──────────┼──────┤
│ 2019-12-15 01:00:00 │ Temporal │ 3 ─  │
│ 2019-12-15 01:00:00 │ Manual   │ 3 ─  │
├─────────────────────┼──────────┼──────┤
│ 2019-12-15 12:00:00 │ Temporal │ 4 +  │
│ 2019-12-15 12:00:00 │ Manual   │ 4 +  │
├─────────────────────┼──────────┼──────┤
│ 2019-12-15 13:00:00 │ Temporal │ 3 ─  │
│ 2019-12-15 13:00:00 │ Manual   │ 4 .  │
├─────────────────────┼──────────┼──────┤
│ 2019-12-15 17:00:00 │ Temporal │ 3 .  │
│ 2019-12-15 17:00:00 │ Manual   │ 3 ─  │
├─────────────────────┼──────────┼──────┤
│ 2019-12-16 00:00:00 │ Temporal │ 3 .  │
│ 2019-12-16 00:00:00 │ Manual   │ 4 +  │
├─────────────────────┼──────────┼──────┤
│ 2019-12-16 01:00:00 │ Temporal │ 3 .  │
│ 2019-12-16 01:00:00 │ Manual   │ 3 ─  │
├─────────────────────┼──────────┼──────┤
│ 2019-12-16 12:00:00 │ Temporal │ 4 +  │
│ 2019-12-16 12:00:00 │ Manual   │ 4 +  │
├─────────────────────┼──────────┼──────┤
│ 2019-12-16 13:00:00 │ Temporal │ 3 ─  │
│ 2019-12-16 13:00:00 │ Manual   │ 4 .  │
└─────────────────────┴──────────┴──────┘
like image 184
Bryan__T Avatar answered Sep 19 '25 20:09

Bryan__T

Sign in to Comment

Related questions

SQL Stored Procedure if statement with bit
SQLCLR - How can I register referenced assemblies of my CLR assembly without setting TRUSTWORTHY ON?
Send email in a tabular format using SQL Server database mail
How to generate create script of tables with their constraints using SQL query in SQL Server 2008
How to use Bulk insert to insert data from Dataframe to SQL Server table?
Assign values to multiple variables from one select statement MS SQL
Count distinct values across more than one column
How to calculate a MSSQL query execution time using python
How to order by date from related table with EF Core and LINQ

Donate For Us

If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!