Sql Server: CDC (Change Data Capture) Configure Retention period

CDC (Change Data Capture) is a pretty robust technology. We’ve been using it for a while now and it works great. As it is built on top of another established robust technology Replication, I was more confident to continue using it in our production environment.

One aspect of CDC that has consumed a little more time for us was the CDC Cleanup operation (purging). Since there are a number of CDC tables available to query the internals of CDC progress, at at given time, it makes troubleshooting easier.

Our environment and my question from a few days ago:

We have CDC enabled on SQL Server 2008 with retention re-configured to a number other than the default 3 days (or something similar).

When we first configured it (with default options), it worked fine. But since the day we changed the retention period from default to something like 25 days, it stopped working correctly. It would run for many hours (15 to 20 hours). So, after a few days we disabled the job (not good, but we had other priorities to deal with than the data retention).

Now after 10 months, the disk is close to getting full. So we need to re-configure it and start purging data. A few days ago, just to test how it works, I ran a test run. I checked the timestamp of the oldest LSN and added a month to it and changed the retention period to something like 10 months + 2 days. So when the clean up job runs, it should come back with success immediately, as there is no data older than 10 months. But it does not. Its still runs for many hours.

Now I am starting to wonder if the retention period (column) in msdb.dbo.cdc_jobs table works or not.Aaron Bertrand mentioned in his blog that he did not have perfect success with this setting. Maybe this setting is not used by the Clean up job.

I posted this question a few days ago and after some experiments, I arrived at this answer and posted it back in the same site (StackOverflow).

Answer:

CDC cleanup job  relies on msdb.dbo.cdc_jobs table. As far as I know, the only way to configure retention period is by updating the retention column in the msdb.dbo.cdc_jobs table. You want to update the retention column of the record with 'cleanup' in job_type column. Retention is mentioned in number of minutes of data to retain. So if you want to retain data only for 1 day, update the column with 1440 (number of minutes in a day).

It could be done in two ways:

EXECUTE sys.sp_cdc_change_job @job_type = N'cleanup', @retention = 349082;
-- or
UPDATE msdb.dbo.cdc_jobs SET retention = '349082' WHERE job_type = 'cleanup'

If you have more than one CDC running, then using the appropriate CDC instance name.

A Little more info:

When the CDC cleanup job runs, it purges all data in the CDC tables up to this retention date. You can stop theCDC cleanup job any time while it is running. When you start it, it will pick up from where it left off. So if you stopped the job while it is running today and start to run it tomorrow it will start from where it left off yesterday and perform purge until the retention period mentioned in the msdn.dbo.cdc_jobs table.

CDC is pretty robust technology.

Hope it help,

_SqlTimes

Read Full Post »

Error while configuring Change Data Capture (CDC) on Sql Server 2008

A few days ago, I was setting up Change Data Capture (CDC) on a new database on my machine and I ran into this error.

Msg 22830, Level 16, State 1, Procedure sp_cdc_enable_db_internal, Line 186
Could not update the metadata that indicates database 'MyDB' is enabled for Change Data Capture.
The failure occurred when executing the command 'SetCDCTracked(Value = 1)'.
The error returned was 15404: 'Could not obtain information about Windows NT group/user 'AD\Sql1', error code 0x6e.'.
Use the action and error to determine the cause of the failure and resubmit the request.

Msg 266, Level 16, State 2, Procedure sp_cdc_enable_db_internal, Line 0
Transaction count after EXECUTE indicates a mismatching number of BEGIN and COMMIT statements.
Previous count = 0, current count = 1.

Msg 266, Level 16, State 2, Procedure sp_cdc_enable_db, Line 0
Transaction count after EXECUTE indicates a mismatching number of BEGIN and COMMIT statements. Previous count = 0, current count = 1.

Msg 3998, Level 16, State 1, Line 1
Uncommittable transaction is detected at the end of the batch. The transaction is rolled back.

This database ‘MyDatabase’ is created using domain account ‘AD\Sql1’ and I am setting up CDC using the same domain account (Windows Authentication). But for some reason CDC could not be enabled on this database. If you check the properties of this database you can see that the owner of this database is still ‘AD\Sql1’.

I am not sure why it is not able to obtain information about this domain account, but once I changed the owner from domain account to ‘sa’, it works !!

EXEC sys.sp_changedbowner 'sa'

Then try this and it works:

EXECUTE sys.sp_cdc_enable_db

Enable_CDC_on_DB

I need to figure out why this error happens and why changing the owner to ‘sa’ works.

Hope this helps,

_SqlTimes

Read Full Post »