Change Data Capture For Updates and Deletes Only

Question

Our database is insert inensive (200-500k per night) but update light (maybe a few hundred per day).

I need to preserve a history of all changes to the inserted rows themselves for an indefinite period of time (but not the actual insert). I would love to use Change Data Capture but the amount of space required to support this is not avaiable. If I can figure out of to do one of the following, my life would be much easier.

1) Limite change data tracking to UPDATES and DELETES only 2) Cleanup only INSERTS from the CDC tables regularly

In the past, I'd have just used a trigger (which is still not off the table!).

Aaron Bertrand · Accepted Answer · 2012-09-11T22:11:43.903

3

I would just use a trigger to capture updates and deletes.

I don't think you can tell CDC what DML to pay attention to, and I think it's quite wasteful to let CDC record all of these inserts only to delete them afterward. That in and of itself is expensive and the fragmentation it will cause will also cause issues for any queries you run against the capture tables (you'll have lots of mostly-empty pages) as well as the work statistics will have to do to constantly keep the stats up to date.

You could possible put an instead of insert trigger on the capture table, that just does nothing, but I haven't tried to do this to even see if it is allowed, and I certainly don't know what impact that will have on the CDC functions. Possibly worth some investigation, but my original answer still stands even if this hack does work: just use a trigger.

edited Sep 11 '12 at 22:11

answered Sep 11 '12 at 22:01

Aaron Bertrand

272,866
37
466
490

Thanks Aaron...that is pretty much where my mind was going anywhere. I really hope that more fine grained control of the CDC will be allowed in the future. – PseudoToad Sep 11 '12 at 23:55
I'm not certain fragmentation is a consideration when implementing the _CT tables in themselves. Depending on the change volume you can strategize the best solution. Triggers are, for their worth, simply an extension of the transaction and being synchronous, as opposed to asynchronous, they carry their own risks. CDC provides many facets, and whereby you cannot simply determine which operations to capture, you should be looking to clear the _CT tables at the earliest opportunity in any case and offload the data into staging or your audit DB. – LogicalMan Apr 20 '17 at 16:50
Obviously you should NEVER need to delete rows by operation from the _CT tables as you can determine which operations you will extract and ignore those you don't need, prior to cleaning the table irrespective of operation, a policy that should be at the forefront of any solution. We complete a refresh of our CDC instances during a maintenance period each week and as such have not suffered slowdowns or other issues using CDC for all our auditing or reporting on client systems. – LogicalMan Apr 20 '17 at 16:57

score 1 · Answer 2 · answered Mar 31 '17 at 00:48

If space is a consideration, you can always assign the CDC tables to work with a different filegroup that could potentially live on a different server. You'd do that this way:

 ALTER DATABASE YourDatabase
 ADD FILEGROUP [cdc_ChangeTables];
 go

 --this step requires going on to somewhere on your hard drive and creating a folder
 ALTER DATABASE YourDatabase
 ADD FILE ( NAME = N'cdc_ChangeTables',
            FILENAME = N'E:\NameOfFolderYouSetUp\YourDatabase_cdc_ChangeTables.mdf',
            SIZE = 1048576KB,
            FILEGROWTH = 102400KB )
 TO FILEGROUP [cdc_ChangeTables];
 GO

Then when you want to set up your CDC tables, you point them toward that filegroup instead:

EXEC sys.sp_cdc_enable_table
    @source_schema = N'dbo',
    @source_name = N'TableYouWantToCapture',
    @role_name = N'cdc_admin',
    @filegroup_name = N'cdc_ChangeTables', --this is where you name the filegroup from previous step
    @supports_net_changes = 1,  
    @capture_instance = N'dbo_TableYouWantToCapture',
    @captured_column_list = 'ColumnName1, ColumnName2'; --comma-delimited list of column names
GO

If you want to query only updates/deletes, you can use the system function like so:

SELECT * 
FROM cdc.fn_cdc_get_all_changes_dbo_TableYouWantToCapture(@from_lsn, @to_lsn, N'all update old')
WHERE __$operation IN (1,3,4)

Change Data Capture For Updates and Deletes Only

2 Answers2

Linked