I have two tables, call them dbo.T1 and dbo.T2. I want to capture delta every few hours using CDC.
The first time I query CDC, I pass in sys.fn_cdc_get_min_lsn('dbo_t1') ( and sys.fn_cdc_get_min_lsn('dbo_t2') ) as @from_lsn for cdc.fn_cdc_get_net_changes_dbo_t1 (and cdc.fn_cdc_get_net_changes_dbo_t2 ) function.
Also, sys.fn_cdc_get_max_lsn() as @to_lsn.
So far so good.
Now, the next time I run the query, I only want to capture the delta since the previous capture. I can still call sys.fn_cdc_get_max_lsn() to get @to_lsn, but I need to get @from_lsn for those tables specifically that is next VALID number greater than the previous capture's max lsn FOR THOSE SPECIFIC TABLES (cannot use the same min fx as before since I want delta after the last capture).
Example:
First time: T1 @from_lsn = 1 (let's use int to make it simple) and T2 @from_lsn = 5. Global @to_lsn = 10
Second time: T1 @from_lsn = 14 and T2 @from_lsn = 12. Global @to_lsn = 20
How do I get those @from_lsn for second+ captures? I can't pass in previous @to_lsn as min because CDC throws error due to invalid value inside those captured instances. Basically, I need to pass in 14 when I query T1 and 12 when I query T2 as @from_lsn the second time.
Use sys.fn_cdc_increment_lsn ( last_to_lsn_value ) . That will get you the next lsn in the sequence. It doesn't matter if that lsn will have any transactions operating against your table. I may or may not. But you want all changes from that lsn to the high water mark for your tables.