Sql-server – Can SQL Server Change Data capture (CDC) log rows to an external DB

I answered this question in my blog at Sqltimes.wordpress.com 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. It could be done in two ways:

EXECUTE sys.sp_cdc_change_job @job_type = N'cleanup', @retention = 349082; UPDATE msdb.dbo.cdc_jobs SET retention = '666000' 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 date. You can stop the CDC 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, _UB

And the moral of the story is... test, try other things, think big, then small, always assume there is a better way.

As scientifically interesting as my last answer was. I decided to try one other approach. I remembered I could do concat with the XML PATH('') trick. Since I knew how to get the ordinal of each changed column from the captured_column list from the previous answer I thought it would be worth testing if the MS bit function would work better that way for what we needed.

SELECT __$update_mask ,
        ( SELECT    CC.column_name + ','
          FROM      cdc.captured_columns CC
                    INNER JOIN cdc.change_tables CT ON CC.[object_id] = CT.[object_id]
          WHERE     capture_instance = 'dbo_OurTableName'
                    AND sys.fn_cdc_is_bit_set(CC.column_ordinal,
                                              PD.__$update_mask) = 1
        FOR
          XML PATH('')
        ) AS changedcolumns
FROM    cdc.dbo_MyTableName PD

It's way cleaner than (though not as fun as) all that CLR, returns the approach back to native SQL code only. And, drum roll.... returns the same results in less than a second. Since the production data is 100 times bigger every second counts.

I'm leaving the other answer up for scientific purposes - but for now, this is our correct answer.