T-SQL Capture Minimum Log Sequence Number of A Table At A Specific Time Using Change Data Capture

Question

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.

Answer 1

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.

See https://learn.microsoft.com/en-us/sql/relational-databases/system-functions/sys-fn-cdc-increment-lsn-transact-sql?view=sql-server-ver15

Answer 2

This is my code I'm currently using. It uses fn_cdc_increment_lsn() to find the next lsn value based on the previous run:

DECLARE @last_known_lsn binary(10), @to_lsn binary(10), @from_lsn binary(10)
SET @last_known_lsn = 0x0000011D00000B080001; -- the @to_lsn value from last run
SET @from_lsn = sys.fn_cdc_increment_lsn(@last_known_lsn);
SET @to_lsn = sys.fn_cdc_get_max_lsn();
SELECT * FROM cdc.fn_cdc_get_net_changes_dbo_customers (@from_lsn, @to_lsn, N'all');