Increase in the volume of lsn_time_mapping cdc table sql server

[Deepanshu Nagpal]

Hi Debezium Team , 

There is considerable increase in the volume of lsn_time_mapping table in sql server due to the enablement of cdc on one heavy table….delete ops is taking time due to that. what is the impact of this table if we daily truncate the table in mignight. What can be the deletion approach for this?

Any help would be appreciated as we are stuck due to this issue. 

Thanks

Deepanshu

[Liudas Sodonis]

Hello Deepanshu,

Not an expert here but from what I understand all the CDC related tables can be cleaned often. 

CDC tables hold the changes so you can "travel back" to read some changes from before to sync up your data.

So when you start your Debezium it locks tables for the initial load and then reads changes from CDC tables. Now if debezium process is stopped for some reason you can start it back and it will not do the initial load again (at least this is not the default configuration). It would check the lsn value it loaded with the current SQL server value and would load from that point. The data gets out of sync if your CDC tables are cleaned before debezium restarts (because those events get lost during cleanup). So according to this, you can decide when to do CDC tables clean up. For some databases, we have a clean-up job for events older than 3 days. For super intense ones we have for 6 hours.

I hope this helps!

Cheers, 

Liudas

[Deepanshu Nagpal]

Hi Liudas,

Thanks a lot for the reply.

The problem here is tha size of lsn_table_mapping has increased drastically due to enablement of CDC in one heavy table and hence, requires a one time truncate. 

Will the data go out of sync if we truncate this table during the non peak hours ?

Thanks

Deepanshu 

--
You received this message because you are subscribed to a topic in the Google Groups "debezium" group.
To unsubscribe from this topic, visit https://groups.google.com/d/topic/debezium/6ctMoBuV-oY/unsubscribe.
To unsubscribe from this group and all its topics, send an email to debezium+u...@googlegroups.com.
To view this discussion on the web visit https://groups.google.com/d/msgid/debezium/4653aaf5-d1af-4c35-8033-44c9c2046dbbn%40googlegroups.com.

[Liudas Sodonis]

I don't think it would. But you should not clean just a single table. You should run clean up job that removes old entries from several tabels, you can find more info here https://learn.microsoft.com/en-us/sql/relational-databases/system-stored-procedures/sys-sp-cdc-add-job-transact-sql?view=sql-server-ver16#b-create-a-cleanup-job 

Cheers,

Liudas

[Deepanshu Nagpal]

Thanks 

Liudas SodoniZ, This is very helpful. 

--