Apr 25, 2012

The retention value specified for the Change Data Capture cleanup process must be greater than 0 and less than or equal to 52594800. When creating or modifying the cleanup job, specify a retention value (in minutes) that is within that range. If this error is encountered when executing the sys.sp_cdc_cleanup_change_table stored procedure, reset the retention value associated with the job to a non-negative value less than 52594800 by using the sp_cdc_change_job stored procedure.


If we have enabled cdc (change data capture) in any database and we want to add or edit retention period for cleanup process by execution:

Now if we will execute following sql query:

EXEC sys.sp_cdc_add_job @job_type = 'Cleanup', @retention = 0

Or

EXEC sys.sp_cdc_add_job @job_type = 'Cleanup', @retention = 52594801

Or

EXEC sys.sp_cdc_change_job @job_type = 'Cleanup', @retention = 0

We will get error message like:

The retention value specified for the Change Data Capture cleanup process must be greater than 0 and less than or equal to 52594800. When creating or modifying the cleanup job, specify a retention value (in minutes) that is within that range. If this error is encountered when executing the sys.sp_cdc_cleanup_change_table stored procedure, reset the retention value associated with the job to a non-negative value less than 52594800 by using the sp_cdc_change_job stored procedure.

Cause: Valid range for retention is:

0 < @retention <= 52594800

Solution:

EXEC sys.sp_cdc_add_job @job_type = 'Cleanup', @retention = 10

Or

EXEC sys.sp_cdc_add_job @job_type = 'Cleanup', @retention = 52594800

Or

EXEC sys.sp_cdc_change_job @job_type = 'Cleanup', @retention = 500

No comments:

Post a Comment