主页 / dba / 问题 / 94141
Accepted
Jānis
Asked: 2015-03-03 00:13:03 +0800 CST

更改跟踪清理

  • 772

有没有办法知道何时清理更改跟踪表？如何检查清理过程的最后一次发生？

背景：

我对数据库进行了更改跟踪，保留时间设置为 1 分钟：

ALTER DATABASE [test] SET CHANGE_TRACKING = ON (CHANGE_RETENTION = 1 MINUTES)

我在我的测试表中进行了一些更新，然后等待比保留期更长的时间（比如说 3 分钟）。由于清理过程是异步的，我明白不能保证更改跟踪表会在 1 分钟后被清理。它可以在> 1 分钟的任何时间发生。

我发现这个过程可以手动调用清理过程：

EXEC sp_flush_commit_table_on_demand 100000

但它没有按预期工作..（更改跟踪行在保留期后仍然存在）

sql-server sql-server-2012

2 个回答

  1. Best Answer

    我使用下面的代码复制了这个，基本上，是的，如果你等待足够长的时间，并且自动清理是打开的，它会自行清理。它似乎受到最低限度的约束 - 也就是说，change_retention 设置更多的是建议而不是命令。

    诚然，我还没有测试过真实世界的表活动等，但下面的链接包含有关这项工作的更多信息 - 它实际上更像是一个黑匣子，而不是任何东西。

    链接：

    MS 更改跟踪

    Kendra Little 谈变更跟踪

    歌词 - 由迪士尼提供

    这就是我想出的。

    use master;
go

drop database cttest;
go

create database cttest;
go

use cttest;
go

create table dbo.t
(id int identity(1,1) primary key--can't track without it
, c1 int
, c2 varchar(100));
go

-- pre ct
insert t values (123, 'Yo ho, yo ho')
insert t values (234, 'We pillage, we plunder, we rifle and loot.')
insert t values (345, 'Drink up me ''earties, yo ho.')
insert t values (456, 'We kidnap and ravage and don''t give a hoot.')
insert t values (567, 'Drink up me ''earties, yo ho.')
go

-- check the table 
select * from t;
go

-- enable change tracking for this DB
alter database cttest set change_tracking = on (change_retention = 1 minutes)
go

alter table dbo.t enable change_tracking with (track_columns_updated=off);
go

-- this should be a tracked change
insert t values (678, 'Yo ho, yo ho, a pirate''s life for me.');
go

-- show the change tracking record
-- so i waited 10 minutes, and then...
select * from changetable(changes dbo.t, NULL) as ct;
go

-- is this table even tracked ?
SELECT object_name(object_id),* FROM sys.change_tracking_tables 

-- make another change
update t set c2 = 'Yo ho, yo ho, a pirate''s life for me.' where c1 = 123;
go 

-- show the change tracking record
select * from changetable(changes dbo.t, NULL) as ct;
go

-- make more changes
insert t values (789, 'We extort and pilfer, we filch and sack.')
insert t values (8910, 'Drink up me ''earties, yo ho.')
insert t values (91011, 'Maraud and embezzle and even highjack.')
insert t values (101112, 'Drink up me ''earties, yo ho.')
go

-- show the change tracking record
select * from changetable(changes dbo.t, NULL) as ct;
go

-- make another change
update t set c2 = 'Yo ho, yo ho, a pirate''s life for me.' where c1 = 123;
go 15

-- show the tracked items
select * from changetable(changes dbo.t, NULL) as ct;
go

-- force a <ahem> flush
EXEC sp_flush_commit_table_on_demand 100000;
go

/*
Received message
(0 row(s) affected)
The Change Tracking manual cleanup procedure for database cttest has been executed
*/

-- show the tracked items
-- still there !
select * from changetable(changes dbo.t, NULL) as ct;
go

-- ok then, we'll turn it off !
alter database cttest set change_tracking (auto_cleanup=off);
go

-- force a <ahem> flush
EXEC sp_flush_commit_table_on_demand 100000;
go

-- show the tracked items
-- still there !
select * from changetable(changes dbo.t, NULL) as ct;
go

-- ok then, we'll turn it back on, and wait
alter database cttest set change_tracking (change_retention = 1 minutes, auto_cleanup = on)
go
    • 2

  2. sp_flush_commit_table_on_demand仅清除syscommittab而不是更改跟踪边表。

    执行sp_flush_commit_table_on_demand与清除更改跟踪边表无关。 CHANGETABLE功能依赖于两者syscommittab并更改跟踪边表。

    select * from changetable(changes dbo.t, NULL) as ct- 这将返回自我们作为最后一个同步版本dbo.t传递以来的第一个版本以来对表的所有更改。NULL

    • 0

相关问题