0

I have enable Change Data Capture on a table. I'm trying to understand cdc.lsn_time_mapping. Unfortunately the documentation is vague and missing a column from the results.

What's the difference between start_lsn and tran_begin_lsn, and how do these relate to __$start_lsn in the change table cdc.<capture_instance>_CT?

1 Answer 1

2

Let's create a concrete, reproducible example to talk about.

create table dbo.t(c int not null primary key);

exec sys.sp_cdc_enable_table
    @source_schema = 'dbo'
    ,@source_name = 't'
    ,@role_name = NULL;

Create an explicit transaction, insert one row and commit. Run the statements one-at-a-time. Wait a few seconds between each -- this will make the following explanation clearer by separating time values.

begin transaction
    -- wait a bit
    insert dbo.t(c) values (1);
    -- tum te tum
commit

Now the change table looks like this.

select * from cdc.dbo_t_ct;

__$start_lsn           __$end_lsn __$seqval              __$operation __$update_mask c   __$command_id
---------------------- ---------- ---------------------- ------------ -------------- --- -------------
0x0000002900000F410001 NULL       0x0000002900000F3B001B 2            0x01           1   1

Every row in a change table will have a matching row in lsn_time_mapping. They match on start_lsn. So we can query it.


select * from cdc.lsn_time_mapping
where start_lsn = 0x0000002900000F410001;

start_lsn              tran_begin_time         tran_end_time           tran_id         tran_begin_lsn
---------------------- ----------------------- ----------------------- --------------- ----------------------
0x0000002900000F410001 2023-09-28 22:47:49.717 2023-09-28 22:47:57.803 0x0000000008C9  0x0000002900000F3B0001

There may be other rows in lsn_time_mapping from other writes to our table, from other change tables, or from CDC internal administration.

tran_id is the SQL Server internal identifier for the database transaction. We can use this (suitably formatted) to retrieve values from the log file.

select
    [Current LSN],
    Operation,
    [Begin Time],
    [End Time]
from sys.fn_dblog(null, null)
where [Transaction ID] = '0000:000008C9'

Current LSN             Operation        Begin Time               End Time
----------------------- ---------------- ------------------------ ------------------------
00000029:00000F3B:0001  LOP_BEGIN_XACT   2023/09/28 22:47:49:717  NULL
00000029:00000F3B:0002  LOP_LOCK_XACT    NULL                     NULL
00000029:00000F3B:001B  LOP_INSERT_ROWS  NULL                     NULL
00000029:00000F41:0001  LOP_COMMIT_XACT  NULL                     2023/09/28 22:47:57:803

Matching the LSNs between this and lsn_time_mapping we see tran_begin_lsn is the LSN that marks the beginning of the transaction, as it says. The corresponding Begin Time and tran_begin_time match, too.

start_lsn, and hence __$start_lsn, mark the end of the transaction, however. So it is a misnomer. This is, arguably, clearer in the Overview than it is in the reference documentation.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.