CDC Retention time

According to BOL the default CDC retention time is 3 days and it was mentioned that it configurable.

Can some of point me on how to change the default value.

Thanks in advance.

You can use sp_cdc_add_job or sp_cdc_change_job to set the retention for the cleanup job.

Thanks

CDC not tracking changes

Hi

I'm trying to get CDC going, it works however when i query the LSN using the functions i get no changes. The min and max LSN returns null. SQL agent is running, db is on full recovery model etc.

any ideas ?

thanks

CDC is a SQL Server 2008 feature. Not to mention nothing to do with SSIS. Can you refine your issue so that we can redirect your question appropriately? This is a SQL Server 2005 forum for the SQL Server Integration Services tool.

|||

sorry Phil, but when i search on the forums under "change data capture" - only SSIS forums come up. I'll ask the question internally - i'm in Microsoft. thanks for your troubles.

|||

Sqlgoof wrote:

sorry Phil, but when i search on the forums under "change data capture" - only SSIS forums come up. I'll ask the question internally - i'm in Microsoft. thanks for your troubles.

Try the SQL Server 2008 Data Warehousing forum: http://forums.microsoft.com/MSDN/ShowForum.aspx?ForumID=1617&SiteID=1

CDC - Inconsistent behaviour (?) in allowing PK modification

I executed below scenarios and the behaviour seems to be inconsistent. I noticed cdc.change_tables tracks table details with index_name but BOL doesn't explain what is/isn't possible interms of modifying PK. Is #1 by design, if so it needs to be clarified.

1. Enable CDC on a table with PK. Later try to disable/drop/change PK definition on base table – It is not allowed
2. Enable CDC on a table with no PK. Later try to create/change/drop PK definition on base table – It is allowed

Note: NET changes is not enabled in both cases.

Thanks,

Siva

let me get back to you on this...|||

I see you've already filed this issue in connect. I'll cut/paste the response here as well for anyone else that's wondering the same thing.

The behavior is by design. When CDC is enabled and if a primary key exists on the table, CDC will use the index regardless of whether net changes is enabled or not.

If there is no primary key on the table, you can still enable CDC but only with net changes set to false. You are then able to create a primary key and alter it since CDC does not use the PK.

This will be documented in BOL.

CDC - Inconsistent behaviour (?) in allowing PK modification

I executed below scenarios and the behaviour seems to be inconsistent. I noticed cdc.change_tables tracks table details with index_name but BOL doesn't explain what is/isn't possible interms of modifying PK. Is #1 by design, if so it needs to be clarified.

1. Enable CDC on a table with PK. Later try to disable/drop/change PK definition on base table – It is not allowed
2. Enable CDC on a table with no PK. Later try to create/change/drop PK definition on base table – It is allowed

Note: NET changes is not enabled in both cases.

Thanks,

Siva

let me get back to you on this...|||

I see you've already filed this issue in connect. I'll cut/paste the response here as well for anyone else that's wondering the same thing.

The behavior is by design. When CDC is enabled and if a primary key exists on the table, CDC will use the index regardless of whether net changes is enabled or not.

If there is no primary key on the table, you can still enable CDC but only with net changes set to false. You are then able to create a primary key and alter it since CDC does not use the PK.

This will be documented in BOL.

CDC - Inconsistent behaviour (?) in allowing PK modification

I executed below scenarios and the behaviour seems to be inconsistent. I noticed cdc.change_tables tracks table details with index_name but BOL doesn't explain what is/isn't possible interms of modifying PK. Is #1 by design, if so it needs to be clarified.

1. Enable CDC on a table with PK. Later try to disable/drop/change PK definition on base table – It is not allowed
2. Enable CDC on a table with no PK. Later try to create/change/drop PK definition on base table – It is allowed

Note: NET changes is not enabled in both cases.

Thanks,

Siva

let me get back to you on this...|||

I see you've already filed this issue in connect. I'll cut/paste the response here as well for anyone else that's wondering the same thing.

The behavior is by design. When CDC is enabled and if a primary key exists on the table, CDC will use the index regardless of whether net changes is enabled or not.

If there is no primary key on the table, you can still enable CDC but only with net changes set to false. You are then able to create a primary key and alter it since CDC does not use the PK.

This will be documented in BOL.