SQL Server 2012 – Change Tracking Cleanup

change-trackingsql serversql-server-2012

Is there a way to know when cleanup of change tracking tables occurs? How to check the last occurrence of a cleanup process?

BACKGROUND:

I have Change Tracking on database with retention set to 1 minute:

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

I do some updates in my test table and then wait a bit longer than the retention period (lets say 3 minutes). As the cleanup process is asynchronous, I do understand that there NO guarantee that Change Tracking tables will be cleaned up after 1 minute. It can happen at any time that is > 1 min.

I found this procedure to call the cleanup process manually:

EXEC sp_flush_commit_table_on_demand 100000

But it does not work as expected.. (Change Tracking rows are still in place after retention period)

Best Answer

I reproduced this using the code below, and essentially, yes, if you wait long enough, and auto cleanup is on, it will clean itself out. It appears to be bound the a minimum - that is, the change_retention setting is more of a suggestion than a command.

Admittedly, I haven't tested with real-world table activity, etc., but the links below contain more information about what the job does - it's more of a black box, really, than anything.

Links:

MS Change Tracking

Kendra Little on Change Tracking

Song Lyrics - Courtesy of Disney

Here's what I came up with.

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
Related Question