Get only 100 records in particular LSN of the CDC

Question

HI Experts,

Am new to CDC has some doubt on this.I have a table and enabled CDC on it.For a single transaction i may get more than 400 records in one shot.for every single transaction i believe a new LSN will be created for each transaction.Am suing the below code for retrieving all the transaction related to that LSn by below code,

SET @FromLsn = TableName.sys.fn_cdc_increment_lsn(@ToLsn)
   

SET @ToLsn = TableName.sys.fn_cdc_map_time_to_lsn('largest less than or equal', 
GETDATE())
Now what i need is to get only 100 records of that particular transaction LSN.How could i do it?

Answer 1

Hi venkatkumar,

Multiple changes within a transaction will share the same commit LSN. For every change on each transaction, there is a record in cdc.<capture_instance>_CT change table, with __$start_lsn column to record the commit LSN that is associated with the change to the source table, and the __$seqval column to order the change within its transaction.

In you scenario, you can get the top 100 records for a particular LSN as:

select top 100 * from  cdc.<capture_instance>_CT where __$start_lsn=@LSN  

Please relace the @LSN with your own LSN.

TechNet Subscriber Support
If you are TechNet Subscription user and have any feedback on our support quality, please send your feedback here.

---

Stephanie Lv

TechNet Community Support