Removes rows from the change table in the current database based on the specified low_water_mark value. This stored procedure is provided for users who want to directly manage the change table cleanup process. Caution should be used, however, because the procedure affects all consumers of the data in the change table.
Applies to: SQL Server (SQL Server 2008 through current version).
sys.sp_cdc_cleanup_change_table performs the following operations:
If the @low_water_mark parameter is not NULL, it sets the value of start_lsn for the capture instance to the new low watermark.
The new low watermark might not be the low watermark that is specified in the stored procedure call. If other entries in the cdc.lsn_time_mapping table share the same commit time, the smallest start_lsn represented in the group of entries is selected as the adjusted low watermark. If the @low_water_mark parameter is NULL or the current low watermark is greater than the new lowwatermark, the start_lsn value for the capture instance is left unchanged.
Change table entries with __$start_lsn values less than the low watermark are then deleted. The delete threshold is used to limit the number of rows deleted in a single transaction. A failure to successfully delete entries is reported, but does not affect any change to the capture instance low watermark that might have been made based on the call.
Use sys.sp_cdc_cleanup_change_table in the following circumstances:
The cleanup Agent job reports delete failures.
An administrator can run this stored procedure explicitly to retry a failed operation. To retry cleanup for a given capture instance, execute sys.sp_cdc_cleanup_change_table, and specify NULL for the @low_water_mark parameter.
The simple retention-based policy used by the cleanup Agent job is not adequate.
Because this stored procedure does cleanup for a single capture instance, it can be used to build a custom cleanup strategy that tailors the rules for cleanup to the individual capture instance.