2

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 link 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)

1
  • I checked it daily for a couple days since I posted and nothing was changing. I checked after the weekend on 1/24 (5 days after original) and it looks like 28 million records were removed. So it looks like it is working, just slowly and not even daily. There's still about 50 million records to remove that are past the retention period. I guess there's not much I can do since I can't delete from the history table directly.
    – Gabe
    Jan 24 at 16:20

1 Answer 1

1

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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.