Sql-server – Persistent Severity 016 alerts: “Change Tracking autocleanup is blocked on side table”

My plan for this, is to add a rowversion to each of the source tables, and display the max rowversion from the source tables.

This is making the assumption that rowversion is unique across the entire database and is not incremented in each table (I need to check if that assumption is correct).

SELECT 
 ft.[col1]
,ft.[col2]
,ft.[col3]
,ft.[col4]
,dt.dim_id
,dt.name
, (SELECT MAX(rv) 
   FROM (VALUES (ft.row_ver), (dt.row_ver)) AS value(rv)) AS max_row_ver
FROM [dbo].[myFirstTable] ft
INNER JOIN [dbo].[dim_table1] dt ON ft.[col5_fk] = dt.dim_id

Obviously this only gives you an identifier to use to track changes, you would still need to create a table to track it.

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