Retention not working on temporal table or very slow?



  • I have a table with retention set to 25 months (from today is 2019-12-19) but when I query the validfrom/validTo dates directly in the history table, I see records with ValidTo dates before that. We have had to enable and disable the temporal feature on the table in the past (over a year ago), but I don't think that should make a difference. history table is columnstore index. On MSSQL 2017 standard edition. Not sure what I'm doing wrong or if I'm misunderstanding how this works. I'm using this https://www.mssqltips.com/sqlservertip/5142/temporal-history-table-retention-in-sql-server-2017/ as a reference.

    by querying the history table directly and grouping by validto, I get these results:

    ![enter image description here

    Note: I ran a similar query on the history table a couple months ago and there were about 2 million records in 2018. Today, there's only about 1.2million, so maybe it is deleting but it's just really slow? This history table is pretty big - about 2 billion records. That seems the most likely explanation but is that expected?

    Note2: we added retention_period after the history table was in place, maybe in 2020?

    abbreviated Schema below:

    IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[accounting].[Transactions]') AND type in (N'U'))
    BEGIN
    CREATE TABLE [accounting].[Transactions](
        [SurrogateTransactionID] [bigint] NOT NULL,
        [ValidFrom] [datetime2](2) GENERATED ALWAYS AS ROW START NOT NULL,
        [ValidTo] [datetime2](2) GENERATED ALWAYS AS ROW END NOT NULL,
    

    CONSTRAINT [PK_Transactions] PRIMARY KEY CLUSTERED
    (
    [SurrogateTransactionID] ASC
    )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, DATA_COMPRESSION = PAGE),
    PERIOD FOR SYSTEM_TIME ([ValidFrom], [ValidTo])
    )
    WITH
    (
    SYSTEM_VERSIONING = ON ( HISTORY_TABLE = [accounting].[Transactions_History] )
    )
    END
    GO

    IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[accounting].[DF_Transactions_ValidFrom]') AND type = 'D')
    BEGIN
    ALTER TABLE [accounting].[Transactions] ADD CONSTRAINT [DF_Transactions_ValidFrom] DEFAULT (DATEADD(SECOND,(-1),SYSUTCDATETIME())) FOR [ValidFrom]
    END
    GO

    IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[accounting].[DF_Transactions_ValidTo]') AND type = 'D')
    BEGIN
    ALTER TABLE [accounting].[Transactions] ADD CONSTRAINT [DF_Transactions_ValidTo] DEFAULT ('9999.12.31 23:59:59.99') FOR [ValidTo]
    END
    GO

    ssms doesn't script out retention but it's like this:

    ALTER TABLE SecureGlobalFinance.accounting.Transactions SET (SYSTEM_VERSIONING = ON (HISTORY_TABLE = accounting.Transactions_History, HISTORY_RETENTION_PERIOD=25MONTHS));  
    

    history table schema below

    IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[accounting].[Transactions_History]') AND type in (N'U'))
    BEGIN
    CREATE TABLE [accounting].[Transactions_History](
        [SurrogateTransactionID] [bigint] NOT NULL,
        [ValidFrom] [datetime2](2) NOT NULL,
        [ValidTo] [datetime2](2) NOT NULL,
    ) 
    END
    GO
    /****** Object:  Index [ix_Transactions_History]    Script Date: 1/19/2022 4:02:48 PM ******/
    IF NOT EXISTS (SELECT * FROM sys.indexes WHERE object_id = OBJECT_ID(N'[accounting].[Transactions_History]') AND name = N'ix_Transactions_History')
    CREATE CLUSTERED COLUMNSTORE INDEX [ix_Transactions_History] ON [accounting].[Transactions_History] WITH (DROP_EXISTING = OFF, COMPRESSION_DELAY = 0
        , DATA_COMPRESSION = COLUMNSTORE)
    GO
    

    temporal table enabled: enter image description here

    history retention is enabled:

    enter image description here

    edit: Related link here regarding cleanup interval. https://stackoverflow.com/questions/59218007/ms-sql-server-temporal-history-retention-cleanup-interval

    Microsoft SQL Server 2017 (RTM-CU9) (KB4341265) - 14.0.3030.27 (X64) Jun 29 2018 18:02:47 Copyright (C) 2017 Microsoft Corporation Standard Edition (64-bit) on Windows Server 2012 R2 Standard 6.3 (Build 9600: ) (Hypervisor)



  • I changed the retention to 13 months. Unfortunately, I didn't check specific timing but it appeared clear out the old data within a week! It appears to be in sync on an ongoing basis, so far.

    For anyone else having issues, I suggest changing the retention if you're able to.

    enter image description here




Suggested Topics

  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2