MS SQL Server 临时历史记录保留清理间隔

情况：

我一直在尝试获取一个临时表以在 1 天的历史保留期内自动清理(用于测试目的(。我相信我已经满足了Microsoft的所有标准，根据MS的文档，

匹配行的标识及其从历史记录表中删除的过程在系统计划和运行的后台任务中透明地进行。

如您所见，在Query 2: Results和Query 3: Results下，历史记录表保留的数据远远超过 1 天标记。临时查询适当地隐藏了保留时段之外的记录，因此我假设 SQL Server 正在识别历史记录保留期。

问题：

我是否错过了关键组件，导致历史记录表无法清除？

一个。临时表正在清理。
神秘的"后台任务"不是每天都在发生/有谁知道这个任务的频率吗？我还没有通过Bing，Google和StackOverflow搜索找到这条信息。
一个。似乎不是每日事件。我已经设置了一个测试环境来推断时间表(有关更新，请参见下文(。

╒═════════╗     
│ 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]
└

更新 2019-12-10 15：23 UTC

你能分享你用来创建表的DDL吗(我对时态参数感兴趣(？ – 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
)
)

更新 2019-12-10 15：41 UTC临时表正在清理，但是我的每小时捕获被 IT 人员善意地打断了......所以我不知道清理究竟什么时候会发生。我现在剩下以下问题：

后台任务的计划究竟是什么？
它是否会在服务/服务器重新启动和/或错过计划时间时触发？

我将再次设置此测试，每小时捕获一次，并提供我的发现的更新。当然，我不是唯一一个有这些数据保留/清理问题的人。记录清理的捕获日志：

┌─────────────────────┬──────────┬────────┬──────┬─────────────────────────┬─────────────────────────┐
│ 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 │
└─────────────────────┴──────────┴────────┴──────┴─────────────────────────┴─────────────────────────┘

后台任务确实在某种松散的"Microsoft质量计划"上启动。大多数清理事件发生在一天的开始(00：00(和中午(12：00(左右，但也有一些发生在其他时间。我不知道为什么开始清理时间历史需要时间，但它正在起作用。我已经从我的每小时日志中提取了发生更改的数据，到下表中。享受！

如果需要任何进一步的数据，请告诉我，我将尽我所能提供。

┌─────────────────────┬──────────┬──────┐
│ 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 .  │
└─────────────────────┴──────────┴──────┘

为了补充您的答案，有一些关于 MSDN 的文档(如何删除过期的行(。该页面提到了Azure SQL，但它也可能适用于本地。

清理过程取决于历史记录表的索引布局。请务必注意，只有具有聚集的历史记录表索引(B 树或列存储(可以具有有限保留策略配置。创建后台任务以执行过期数据清理对于具有有限保留期的所有时态表。清理逻辑对于行存储(B 树(聚集索引删除较小的旧行块(高达 10K(，最大限度地减少数据库日志和 IO 的压力子系统。尽管清理逻辑使用所需的 B 树索引，早于保留期的行的删除顺序不能坚定保证。因此，不要依赖清理在您的应用程序中订购。

聚集列存储的清理任务将删除整行一次组(通常每个组包含 100 万行(，即非常高效，尤其是当历史数据以高步伐。

相关内容

最新更新

热门标签：