Post reply

CDC confusion - operation sequence of 3,4,1,2 returned as 1,4 by net changes function

  • November 29, 2015 at 10:56 pm

    #302552

    I'm struggling to understand what I'm seeing in CDC's returned data and I wonder if anyone can tell me whether they can make sense of it.

    I am seeing the following output from a query directly against the change table for a given primary key value - An update followed by a delete and then an insert, (but the delete and insert have the same start LSN):

    __$start_lsn __$seqval __$operation __$update_mask

    ---------------------- ---------------------- ------------ ---------------------------------------------

    0x00BE03A8000457990085 0x00BE03A800045799001E 3 0x000000000000000000000000000000001000000000

    0x00BE03A8000457990085 0x00BE03A800045799001E 4 0x000000000000000000000000000000001000000000

    0x00BE04EA00005BC40059 0x00BE04EA00005B030173 1 0x01FFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFF

    0x00BE04EA00005BC40059 0x00BE04EA00005B030173 2 0x01FFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFF

    If I execute a call to get net changes with the arguments 0x00BE03A8000457990085, 0x00BE04EA00005BC40059, 'all', I get the follwing:

    __$start_lsn __$operation __$update_mask

    ---------------------- ------------ -------------------

    0x00BE04EA00005BC40059 1 NULL

    0x00BE04EA00005BC40059 4 NULL

    I'm not entirely sure about this, but I would expect to see just a single update (4) row from the get net changes function, given that the start and end lsn passed as the arguments specify an interval wherein the row existed at both the start and end of the interval. But I'm also confused about the "delete followed by insert" recorded in the change table, especially since both rows have not only the same (natural) primary key value, but also the same surrogate (identity column) value.

    My best guess is that something in the system code might be doing weird stuff with identity insert and reusing the same natural key, and that this is confusing CDC. Can anyone clarify what I'm seeing here?

  • December 1, 2015 at 6:21 pm

    #1843373

    Found the answer: https://connect.microsoft.com/SQLServer/Feedback/Details/690476

    A bug in the CDC implementation... marked as "external" by Microsoft but possibly stealthily fixed in a CU or SP for 2012.

Viewing 2 posts - 1 through 1 (of 1 total)

You must be logged in to reply to this topic. Login to reply