0

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)

2

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
  • Thanks for answer and links! Kendra Little on Change Tracking article is great. And seems can use sys.dm_tran_commit_table to see oldest transaction in change tracking table. – Jānis Mar 5 '15 at 7:45
0

sp_flush_commit_table_on_demand clears only the syscommittab and not the change tracking side tables.

Executing the sp_flush_commit_table_on_demand has nothing to do with clearing the change tracking side tables. CHANGETABLE function relies on both syscommittab and change tracking side tables.

select * from changetable(changes dbo.t, NULL) as ct - This will return all changes to dbo.t table since the first version since we pass NULL as the last synced version.

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.