Although CDC works and all transactions are logged, we cannot clear the respective CDC table using the builtin procedure
sys.sp_cdc_cleanup_change_table which should remove all the transaction except the last one. We also tried reducing the retention time to 2 minutes to see if that cleared all the transaction except the last one. It didn't.
We have CDC enabled on the database.
Then we have enabled it on the table
Packet_Store_Distribution using the following command:
EXEC sys.sp_cdc_enable_table @source_schema = N'dbo', @source_name = N'Packet_Store_Distribution', @role_name = NULL, @supports_net_changes = 0
This works perfectly and all transactions are logged in the
cdc.dbo_Packet_Store_Distribution_CT table as it should.
But when we try to clear this table using the query below, all the transactions still remain.
DECLARE @lowWaterMark binary(10); SELECT @lowWaterMark = sys.fn_cdc_get_max_lsn(); EXEC sys.sp_cdc_cleanup_change_table @capture_instance = 'dbo_Packet_Store_Distribution_CT', @low_water_mark = @lowWaterMark;
We also tried the following query.
DECLARE @maxLSN binary(10); SELECT @maxLSN = MAX(start_lsn) FROM cdc.lsn_time_mapping; EXEC sys.sp_cdc_cleanup_change_table @capture_instance = 'dbo_Packet_Store_Distribution_CT', @low_water_mark = @maxLSN;
Neither worked and all the transactions still remained where only the last transaction should have.
We also tried to change the retention time to 2 minutes to see if that clears all the transaction except the last one with 2 minutes interval. Still this did not work. The query is given below.
EXECUTE sys.sp_cdc_change_job @job_type = N'cleanup', @retention = 2;
What can cause this?