Snippet Name: Query_CDC_Retention_Period

Tags:

Created Date: 2025-06-16 13:12:50
Last Modified Date: 2025-05-31 10:32:19

Dependencies:

DBA Notes:

Sample Output:

Title: Query CDC Retention Period
Author: user
for sql server where can i query teh cdc retention period

Author: ChatGPT
In SQL Server, the Change Data Capture (CDC) **retention period** is managed at the database level and determines how long captured change data is kept in the CDC tables before it is automatically cleaned up.

You can query the current **retention period** and related settings by querying the system function:

```sql
SELECT 
    [capture_instance], 
    [retention],                 -- in minutes
    [threshold], 
    [role_name]
FROM 
    sys.sp_cdc_help_change_data_capture;
```

However, `sys.sp_cdc_help_change_data_capture` shows info per capture instance. To get the **global CDC retention period** (which is used by the cleanup job), you need to query the `cdc.cdc_jobs` table:

```sql
SELECT 
    job_type,
    retention,
    retention_unit = 'minutes',
    threshold,
    is_active
FROM 
    cdc.cdc_jobs
WHERE 
    job_type = 'cleanup';
```

### Explanation:
- `retention`: The retention period **in minutes**.
- `threshold`: Minimum number of rows before the cleanup job kicks in.
- `is_active`: Whether the job is currently enabled.

### To modify the retention period:
You can use the stored procedure `sys.sp_cdc_change_job`:

```sql
EXEC sys.sp_cdc_change_job   
    @job_type = N'cleanup',   
    @retention = 4320;  -- Example: 3 days (in minutes)
```

Let me know if you also want a query that shows the next scheduled run time for the cleanup job or its success/failure history.

Author: user
is the view different for AWs RDS SQL ServeR?

Author: ChatGPT
Yes ??? **on AWS RDS for SQL Server**, you do **not** have direct access to the SQL Server Agent or its jobs, which includes the internal CDC cleanup job. Therefore, certain system views or stored procedures like `cdc.cdc_jobs` or `sys.sp_cdc_change_job` may **not be accessible** or will return limited information.

Here???s what you need to know for **CDC on RDS SQL Server**:

---

### ??? What *does* work:

#### 1. You can still query the CDC retention period **per capture instance** using:

```sql
EXEC sys.sp_cdc_help_change_data_capture;
```

Each row will show the capture instance, retention period (in minutes), and other settings.

---

### ??? What *does not* work directly:

- `SELECT * FROM cdc.cdc_jobs`  
  ???? Not accessible ??? you'll likely get a permissions error on RDS.

- `EXEC sys.sp_cdc_change_job`  
  ???? Not allowed ??? SQL Server Agent jobs are managed by RDS internally.

---

### ??? How to change CDC retention in RDS SQL Server:

Instead of using `sp_cdc_change_job`, you must use the **RDS stored procedure**:

```sql
EXEC msdb.dbo.rds_cdc_change_job 
    @job_type = 'cleanup', 
    @retention = 4320;  -- Example: 3 days in minutes
```

This is part of the **Amazon RDS for SQL Server extended stored procedures**.

You can verify the current CDC retention setting using:

```sql
EXEC msdb.dbo.rds_cdc_help_jobs;
```

---

### Summary Table

| Action                                 | On-Prem SQL Server | AWS RDS SQL Server           |
|----------------------------------------|--------------------|------------------------------|
| View CDC jobs (`cdc.cdc_jobs`)         | ??? Yes             | ??? No                        |
| Change retention (`sp_cdc_change_job`) | ??? Yes             | ??? No                        |
| View capture settings                  | ??? Yes (`sp_cdc_help_change_data_capture`) | ??? Yes |
| RDS replacement for job changes        | ??? N/A             | ??? `rds_cdc_change_job`      |
| RDS view job info                      | ??? N/A             | ??? `rds_cdc_help_jobs`       |

---

Let me know if you???d like a wrapper script to automate checking or adjusting the retention setting across multiple databases.

Author: user
what is teh default retention period for cdc in AW sRDs

Author: ChatGPT
The **default CDC retention period** for **AWS RDS SQL Server** is:

> **3 days (4,320 minutes)**

This is the same default as on on-premises SQL Server. It controls how long change rows remain in the change tables before being cleaned up by the CDC cleanup job.

---

### ???? To verify current retention in AWS RDS SQL Server:

You can use:

```sql
EXEC msdb.dbo.rds_cdc_help_jobs;
```

It returns something like:

| job_type | is_enabled | retention | threshold |
|----------|------------|-----------|-----------|
| cleanup  | 1          | 4320      | 5000      |
| capture  | 1          | NULL      | NULL      |

- `retention` is in **minutes**
- `threshold` is the row count that triggers cleanup

---

### ??? If you want to change it:

Use:

```sql
EXEC msdb.dbo.rds_cdc_change_job 
    @job_type = 'cleanup', 
    @retention = 10080; -- Example: 7 days
```

Let me know if you want a retention monitor that alerts if it was changed from default.