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:
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:
history retention is enabled:
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)
