How SQL Server Captures Row Modifications Asynchronously via the Transaction Log

In high-throughput enterprise relational databases, tracking row modifications (INSERT, UPDATE, and DELETE) is essential for auditing, event streaming, and downstream synchronization. While application triggers or custom timestamp columns have historically been used for this purpose, they introduce significant transaction overhead and lock contention.

Change Data Capture (CDC) in SQL Server provides an asynchronous, log-based alternative. Operating directly on the database engine level, CDC extracts mutations from the transaction log without blocking active user transactions.

1. Internal Infrastructure: The cdc Schema

When CDC is enabled on a database and a target table using system stored procedures, SQL Server automatically creates a dedicated system schema named cdc.

-- Step 1: Enable CDC at Database Level
EXEC sys.sp_cdc_enable_db;
GO

-- Step 2: Enable CDC on a Specific Table
EXEC sys.sp_cdc_enable_table
    @source_schema = N'dbo',
    @source_name   = N'Orders',
    @role_name     = NULL; -- Optional gating security role
GO

System Objects Generated

Enabling CDC initializes several metadata objects inside the database:

2. The Internal Data Pipeline

CDC operates asynchronously to avoid impacting user write latency. The engine uses a background log-scanning mechanism rather than synchronous triggers.

Image 05-08-26 at 9.22 PM

Execution Steps

  1. Transaction Logging: When a transaction updates a tracked table, SQL Server writes the operational log record to the active transaction log file (.ldf) as normal and assigns it a sequential Log Sequence Number (LSN).

  2. Log Scanning (sys.sp_cdc_scan): A dedicated SQL Server Agent job continually runs sys.sp_cdc_scan. This process reads committed log records associated with CDC-enabled tables asynchronously.

  3. Shadow Table Insertion: The capture job parses the before- and after-images of modified rows from the transaction log and appends them to the corresponding cdc.<capture_instance>_CT table.

3. Dissecting the Change Table (_CT) Schema

The auto-generated change table mirrors the data columns of the source table, augmented with five system metadata columns at the front:

Metadata ColumnData TypeDescription
__$start_lsnbinary(10)The Log Sequence Number of the commit transaction. Defines physical execution order.
__$seqvalbinary(10)Sequence value used to order distinct operations occurring within the same transaction.
__$operationintInteger flag indicating the DML operation type:• 1 = DELETE• 2 = INSERT• 3 = UPDATE (Before Image)• 4 = UPDATE (After Image)
__$update_maskvarbinary(128)A column-level bitmask identifying exactly which columns were modified during an update.
__$command_idintInternal identifier for the T-SQL command within the transaction.

DML Representation Example

When an UPDATE operation changes an order amount from $150.00 to $200.00, CDC writes two rows to the change table:

Image 05-08-26 at 9.24 PM

4. Querying Captured Data Programmatically

Directly querying cdc.<capture_instance>_CT tables is discouraged because underlying schema metadata can shift. Instead, SQL Server automatically generates system Table-Valued Functions (TVFs).

A. Fetching All Detailed Changes

To extract every historical row mutation between two LSN boundaries:

-- 1. Determine active LSN boundaries
DECLARE @from_lsn binary(10) = sys.fn_cdc_get_min_lsn('dbo_Orders');
DECLARE @to_lsn   binary(10) = sys.fn_cdc_get_max_lsn();

-- 2. Extract detailed change records
SELECT 
    sys.fn_cdc_map_lsn_to_time(__$start_lsn) AS CommitTime,
    CASE __$operation
        WHEN 1 THEN 'DELETE'
        WHEN 2 THEN 'INSERT'
        WHEN 3 THEN 'UPDATE (Before)'
        WHEN 4 THEN 'UPDATE (After)'
    END AS Operation,
    OrderID,
    CustomerName,
    Amount
FROM cdc.fn_cdc_get_all_changes_dbo_Orders(@from_lsn, @to_lsn, N'all')
ORDER BY __$start_lsn, __$seqval;

B. Fetching Net Changes

If a single record undergoes dozens of updates within a processing window, running fn_cdc_get_net_changes collapses intermediate states and returns only the final net state of the modified row:

SELECT 
    CASE __$operation
        WHEN 1 THEN 'DELETE'
        WHEN 2 THEN 'INSERT'
        WHEN 4 THEN 'UPDATE'
    END AS NetOperation,
    OrderID,
    CustomerName,
    Amount
FROM cdc.fn_cdc_get_net_changes_dbo_Orders(@from_lsn, @to_lsn, N'all');

5. Retention and Storage Management

Because change tables continuously capture row history, SQL Server provisions an automated Cleanup Job (sys.sp_cdc_cleanup_change_table).