How to Modify Change Data Capture (CDC) Retention Period in SQL Server - SQL Server Tutorial

Scenario : 

You are working as SQL Server DBA or developer, you just enabled Change Data Capture on one of the database. The default retention period is 3 days. You would like to change the retention period to 1000 days. How would you do that?

Solution:

The below script can be used to change the retention period for Change Data Capture ( CDC) in SQL Server database.

/*---------------------------------------
Change CDC Retention Period
The retention is provide in minutes , If we are providing in Days then we 

have to convert that to minutes.
-----------------------------------------*/

USE [DatabaseName]

GO

DECLARE @Retention_IN_DAYS INT
DECLARE @Retention_IN_Minutes INT

SET @Retention_IN_DAYS=10--> Provide the Retention period in days here

SET @Retention_IN_Minutes=@Retention_IN_DAYS * 24 * 60
--Print Current Retention Period

SELECT retention / 60 / 24 AS [Retention_In_Days]

FROM   msdb.dbo.cdc_jobs

WHERE  job_type = 'cleanup'
--SET Retention to Required Days

EXECUTE sys.sp_cdc_change_job
  N'cleanup',
  @retention = @Retention_IN_Minutes
--Check if Retention is Set for Given Days

SELECT retention / 60 / 24 AS [Retention_In_Days]

FROM   msdb.dbo.cdc_jobs

WHERE  job_type = 'cleanup'