1

Scenario

We recently performed a heavy update operation updating two columns on a 2 tb user table. The User table is CDC enabled, which caused Usertable_CT table to bulk up. The User table is a heavy traffic table in production environment.

Current configuration of CDC allows data to be retained for 10 days. We don't want to wait for the CDC cleaning process until the data reaches the threshold

Question

a) Is there a way to identify these records in the _CT table, to help delete these records from the _CT table ?

My finding

cdc.lsn_time_mapping and Usertable_CT both have [start_lsn] which can be compared to capture the Transaction details (tran_begin_time, tran_end_time, tran_id, tran_begin_lsn) from cdc.lsn_time_mapping, could not find further links to discover User or session details which can uniquely identify the records

b) Is there a sql code that can help in avoiding capturing changes on a CDC enable User table for a particular operation instead of disabling and enabling CDC every time, In our case not capturing the update operation.

Will appreciate an answer to fix this problem and helping getting more knowledge and correct me on my findings

Thank you all for the time and efforts

2
  • 1
    b.) I don't think you can disable CDC for a specific operation or spid, but triggers come in handy for this type of case. But let me put it this way, if we don't capture this particular change will that defeat the purpose of auditing?
    – Biju jose
    Jul 8, 2021 at 9:32
  • Thank you for the reply Biju jose, ' if we don't capture this particular change will that defeat the purpose of auditing?' for our scenario the outcome is acceptable
    – user234005
    Jul 8, 2021 at 9:42

2 Answers 2

2

The _CT table will have all the "before" and "after" values from the update. The same WHERE clause that found the rows in the base table will be able to identify the rows in the _CT table.

The retention period in the CT table is configurable. You could set it to a shorter interval, let CDC clean-up run until it has caught up, then reset it to 10 days again. Note this will remove all rows, the ones due to the large update and all the ones due to normal business.

1
  • As mentioned earlier the update operation was performed on two columns of a User table identifying the rows based on a unique identity column, same logic wont be applicable on the CT table since there is a possibility that there can be modification to other columns in the base table which can generate a record in CT table for the same row, which will be hard to identify in our scenario
    – user234005
    Jul 9, 2021 at 1:56
0

Since we were unable to fix the problem, we found a way around it. After we found and confirmed that the consumers of the CDC data do not need the data from the columns which we are working with. But CDC data from rest of the columns of the Usertable is critical, we planned our work around as mentioned below.

We created a new CDC instance for that table without the two column which we were working on and allowed both the instance to run parallelly capturing the CDC data. We made sure that the Lsn matched for both the captured CDC instance asking the consumer of the CDC data to point to the New instance (switching the CDC datasource is dependent on the LSN id ). Later dropping the Old instance for the Usertable.

Finally all the operations on the Usertable relevant to the two columns are no more monitored, allowing us to continue with our update operation.

Note : The two columns which we are working on will no longer be populated by the application ( deprecated columns), so our activity is to perform a cleanup on historical data.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.