2

I am using SQL CDC to track changes for multiple tables in SQL Server. I would want to report out these changes in right sequence for each I have a program which collects the data from each CDC table. But I want to make sure that all the changes that are happening to these tables are reported in correct sequence. Can I rely on LSN for the right sequence?

shA.t
  • 16,580
  • 5
  • 54
  • 111
rauts
  • 1,018
  • 9
  • 21

4 Answers4

5

The LSN number is unique for a given transaction but is not globally unique. If you have multiple records within the same transaction they will all share the same __$start_lsn value in cdc. If you want the correct order of operations you need to sort by __$start_lsn, __$seqval, then __$operation. The __$seqval represents the id of the individual operation within the wrapping transaction.

For example, I have a table in the dbo schema named foo. It has one column y. If I run this statement:

INSERT INTO dbo.foo VALUES (1); 
INSERT INTO dbo.foo VALUES (2);

Then I will see two separate LSN values in cdc because these are in two separate transactions. If I run this:

BEGIN TRAN
INSERT INTO dbo.foo VALUES (1);
INSERT INTO dbo.foo VALUES (2);
COMMIT TRAN

Then I will see one LSN value for both records, but they will have different __$seqval values, and the seqval for my first record will be less than the seqval for my second record.

MJH
  • 1,710
  • 1
  • 9
  • 19
Trevor
  • 51
  • 1
  • 1
2

LSN is unique, ever increasing within the database, across all tables in that database.

Hein
  • 1,453
  • 8
  • 8
1

In most cases LSN value is unique across all tables, however I found instances where one single LSN value belongs to the changes in 40 tables. I don't know the SQL script that associated with those changes, but I know that all operations were 'INSERT'.

Not sure if it is a bug. CDC documentations is poor, covers just basics. Not many users know that CDC capture process has many bugs confirmed by MS for both SQL 2014 & 2016 (we have the open case).

So I would not rely on the documentation. It may be wrong in some scenarios. It's better to implement more checks and test it with large volume of different combinations of changes.

Anton
  • 2,846
  • 1
  • 10
  • 15
0

I also encountered that scenario. In my experience and what I understood is in your first example, there are 2 transactions happened so you will really get 2 different LSN. While in your second example, you only have 1 transaction with 2 queries inside. The CDC will count it as only 1 transaction since it is inside BEGIN and END TRAN. I can't provide links to you since this is my personal experience.

Dharman
  • 30,962
  • 25
  • 85
  • 135
christine
  • 1
  • 2