CODEWITHSUNDEEP
sqlsql-servert-sqlchange-data-capture
user2637453
user2637453

Reputation: 345

CDC LSNs: queries return different minimum value

I have CDC enable on a table and I'm trying to get the minimum LSN for that table to use in an ETL job. However when I run this query

select sys.fn_cdc_get_min_lsn('dbo_Table')

I get a different result to this query

select min(__$start_lsn) from cdc.dbo_Table_CT

Shouldn't these queries return the same values? and if they don't why not? and how to get them back in sync?

Upvotes: 0

Views: 256

Answers (1)

Eric Brandt
Eric Brandt

Reputation: 8101

The first query:

select sys.fn_cdc_get_min_lsn('dbo_Table')

Invokes a system function that returns the lowest POSSIBLE lsn for the capture instance. This value is set when the cleanup function runs. It is recorded in, and queried from, cdc.change_tables.

The second query:

select min(__$start_lsn) from cdc.dbo_Table_CT

Looks at the actual capture instance and returns the lowest ACTUAL lsn for the instance. This value is set when the first actual change to the instance is logged after the cleanup function runs. It is recorded in, and queried from, cdc.dbo_Table_CT.

They're unlikely to tie out, statistically speaking. For the purposes of an ETL job, using the call to the system table will likely be quicker, and is a more accurate reflection of when the current set of change records started being accumulated.

Upvotes: 1

Related Questions