How to clean up all entries in a cdc table in MS SQL?

Question

Reading Microsoft Docs this is the relevant system procedure: sys.sp_cdc_cleanup_change_table

I tried using it like this:

DECLARE @max_lsn binary(10);
SET @max_lsn = sys.fn_cdc_get_max_lsn();
Exec sys.sp_cdc_cleanup_change_table
@capture_instance = N'dbo_mytable',
@low_water_mark = @max_lsn;

The query is executed successfully but checking the table again with the query:

DECLARE @from_lsn binary(10), @to_lsn binary(10);
SET @from_lsn = sys.fn_cdc_get_min_lsn('dbo_mytable');
SET @to_lsn   = sys.fn_cdc_get_max_lsn();
SELECT * FROM cdc.fn_cdc_get_all_changes_dbo_mytable
  (@from_lsn, @to_lsn, N'all');

Still returns a non empty table. I'm not familiar with SQL much. What am I missing?

Answer 1

I built a little test for this, and yes, I saw the same thing. It took me a couple of minutes to figure out what was going on.

The "gotcha" is this little entry in the docs:

If other entries in cdc.lsn_time_mapping share the same commit time as the entry identified by the new low watermark, the smallest LSN associated with that group of entries is chosen as the low watermark.

In other words, if the result of sys.fn_cdc_get_max_lsn() maps to a cdc.lsn_time_mapping.tran_begin_time that also has other start_lsn values associated with it, then the cleanup proc won't actually use the value of sys.fn_cdc_get_max_lsn() as the new low water mark.

In other other words, if the max lsn currently in the change table you want to clean up has the same tran_begin_time as other LSN's, and it is not the lowest of those LSNs, you cannot get a "complete" cleanup of the change table.

The easiest way to get a complete cleanup in those cases is probably to make a minor change to the target table to advance the max lsn and force a new entry, and "hope" that the new entry isn't also associated with any other LSNs with the same tran begin time.

To make that more explicit, here's my little test. Running it over and over has a result that in some cases cleanup is predicted to fail (and fails) and in other cases it is predicted to succeed (and succeeds).

/*
one time setup:

create table t(i int primary key, c char);
create table u(i int primary key, d char);
go

exec sp_cdc_enable_db;
go

exec sys.sp_cdc_enable_table @source_schema = 'dbo', 
                             @source_name = 't',     
                             @supports_net_changes = 1,
                             @role_name = null;

exec sys.sp_cdc_enable_table @source_schema = 'dbo',   
                             @source_name = 'u',       
                             @supports_net_changes = 1,
                             @role_name = null;
*/
set nocount on;

delete from t;
delete from u;
go

insert t select 1, 'a';
insert u select 1, 'b';
waitfor delay '00:00:01';
go

declare @fail int;

select   @fail = count(*)
from     cdc.lsn_time_mapping 
where    tran_begin_time = (
   select   tran_begin_time 
   from     cdc.lsn_time_mapping 
   where    start_lsn = sys.fn_cdc_get_max_lsn()
);

print iif(@fail > 1, 'this wont do the cleanup you expect', 'this will do the cleanup');

DECLARE @max_lsn binary(10) = sys.fn_cdc_get_max_lsn();
Exec sys.sp_cdc_cleanup_change_table
   @capture_instance = N'dbo_t',
   @low_water_mark = @max_lsn;
go

if exists (select * from cdc.dbo_t_ct) print 'did not cleanup';
else print 'did the cleanup';