Change Data Capture Cost: Log, Storage and Cleanup Measured

Change Data Capture cost shows up in places a quick setup never checks. I turned CDC on for one 200,000-row table on SQL Server 2025. Then I measured the log, the storage and the cleanup. The same update wrote three times the log, and the change table grew bigger than the table it tracks.

What CDC Does Behind the Scenes

CDC reads changes from the transaction log and copies them into a change table. A capture job does that reading, and a cleanup job deletes old changes. Each of those steps has a price. None of it shows up when you enable CDC, because the bill arrives later.

The Test Setup

The test runs on SQL Server 2025. It uses a database in SIMPLE recovery and one orders table with 200,000 rows. SQL Agent was stopped on purpose. That way the capture job couldn’t run, and I ran the capture by hand when I wanted it. Run this on a test server only.

CREATE DATABASE CdcCostLab;
ALTER DATABASE CdcCostLab SET RECOVERY SIMPLE;
ALTER DATABASE CdcCostLab MODIFY FILE (NAME = CdcCostLab_log, SIZE = 64MB, FILEGROWTH = 64MB);
GO
USE CdcCostLab;
CREATE TABLE dbo.ShopOrder
(
    OrderID    int IDENTITY(1,1) NOT NULL CONSTRAINT PK_ShopOrder PRIMARY KEY,
    CustomerID int           NOT NULL,
    OrderDate  datetime2(0)  NOT NULL,
    Status     tinyint       NOT NULL,
    Amount     decimal(10,2) NOT NULL,
    Notes      varchar(200)  NOT NULL
);
INSERT dbo.ShopOrder (CustomerID, OrderDate, Status, Amount, Notes)
SELECT TOP (200000)
       ABS(CHECKSUM(NEWID())) % 5000 + 1,
       DATEADD(MINUTE, -(ROW_NUMBER() OVER (ORDER BY (SELECT NULL))), '2026-10-01'),
       1,
       CAST(ABS(CHECKSUM(NEWID())) % 50000 AS decimal(10,2)) / 100,
       REPLICATE('n', 120)
FROM sys.all_columns AS a CROSS JOIN sys.all_columns AS b;
CHECKPOINT;
DBCC SHRINKFILE (CdcCostLab_log, 64) WITH NO_INFOMSGS;

The load grows the log file, so the last line shrinks it back to 64 MB. That gives every later test the same starting point.

To measure the log one statement writes, I read it from inside its own transaction. The query below updates 50,000 rows and reports the log bytes before it commits. Run it once now, before CDC is on. In my run it wrote 6.2 MB.

BEGIN TRAN;
UPDATE dbo.ShopOrder SET Status = 2, Amount = Amount + 1 WHERE OrderID BETWEEN 1 AND 50000;
SELECT CAST(database_transaction_log_bytes_used / 1048576.0 AS decimal(9,1)) AS log_mb
FROM sys.dm_tran_database_transactions
WHERE database_id = DB_ID() AND transaction_id = CURRENT_TRANSACTION_ID();
COMMIT;

Cost 1: Every Update Writes More Log

Now turn CDC on for the database and the table. With SQL Agent stopped, the procedures warn that the agent isn’t running. That’s expected here. Is SQL Agent running on your test server? Then stop the capture job right after, so the backlog can build. The command is EXEC sys.sp_cdc_stop_job @job_type = N'capture';

EXEC sys.sp_cdc_enable_db;
EXEC sys.sp_cdc_enable_table @source_schema = N'dbo', @source_name = N'ShopOrder',
     @role_name = NULL, @capture_instance = N'dbo_ShopOrder_all';

Next, run the measuring query again with the range changed to 50001 AND 100000. It wrote 19.5 MB of log. To be sure, I copied the table into a twin with no CDC. Then I ran both updates in turns, three times each. Every run gave 6.2 MB without CDC and 19.5 MB with it.

The log records explain it. For 1,000 updated rows, both tables wrote 1,000 row changes. Without CDC, each record was 120 bytes. With CDC, each one was 424 bytes. The record carries more of the row, so the capture can rebuild the before and after values later.

To be fair to CDC, the update itself didn’t get slower. It took 70 to 147 milliseconds with CDC and 75 to 94 without. At this size, that’s noise. The cost lands on the log, and the log is shared by everything in the database.

Cost 2: The Log Waits for the Capture

In SIMPLE recovery, a checkpoint normally lets SQL Server reuse the log. With CDC on, it can’t reuse the part the capture hasn’t read yet. This query shows how full the log is and what it’s waiting for.

CHECKPOINT;
SELECT CAST(u.total_log_size_in_bytes / 1048576.0 AS decimal(9,1)) AS log_file_mb,
       CAST(u.used_log_space_in_bytes / 1048576.0 AS decimal(9,1)) AS log_used_mb,
       d.log_reuse_wait_desc
FROM sys.dm_db_log_space_usage AS u
CROSS JOIN sys.databases AS d
WHERE d.database_id = DB_ID();

Before CDC, the answer after a checkpoint was NOTHING. With CDC on and no capture running, it said REPLICATION, and about 24.5 MB stayed in use. Then I kept the updates coming, still with no capture.

DECLARE @round int = 1;
WHILE @round <= 3
BEGIN
    UPDATE dbo.ShopOrder SET Amount = Amount + 1 WHERE OrderID BETWEEN 1 AND 50000;
    CHECKPOINT;
    SET @round += 1;
END;

Run the log query again after the loop. In my runs, about 84 MB of log was in use. The file had grown from 64 MB to 128 MB. Every checkpoint ran, and none of them could free the log.

On a real server, this happens when SQL Agent stops or the capture job fails. Nothing breaks at first. The log grows until it fills the disk. If you see REPLICATION on a database with no replication, check the CDC capture job first. The post on CDC capture lag shows how to read how far behind it is.

Cost 3: The Capture Writes Its Own Log

The capture job runs a procedure named sys.sp_cdc_scan. You can run it yourself, which is what I did. One scan harvested all 200,000 updates in 7 to 8 seconds.

EXEC sys.sp_cdc_scan @maxtrans = 1000000, @maxscans = 100, @continuous = 0;

Here’s the surprise. During the scan, log use rose to about 318 MB and the file grew to 448 MB. The capture inserts every change into the change table, and those inserts are logged too. After a checkpoint, the used space fell to about 6 MB and the wait went back to NOTHING. The file stayed at 448 MB.

So CDC pays for the log twice: once when the update runs, and again when the capture copies it. A long capture backlog makes the second bill arrive all at once.

Cost 4: The Change Table Outgrows the Table

An update creates two rows in the change table: one with the old values and one with the new. Operation 3 is the before image and operation 4 is the after image. After the scan, my change table held 400,000 rows for 200,000 updates.

SELECT OBJECT_NAME(p.object_id) AS table_name,
       SUM(CASE WHEN p.index_id IN (0, 1) THEN p.row_count END) AS row_count,
       CAST(SUM(p.reserved_page_count) * 8 / 1024.0 AS decimal(9,1)) AS reserved_mb
FROM sys.dm_db_partition_stats AS p
WHERE p.object_id IN (SELECT object_id FROM cdc.change_tables)
   OR p.object_id = OBJECT_ID(N'dbo.ShopOrder')
GROUP BY p.object_id;

The orders table reserved 30.9 MB. Its change table reserved 116.8 MB. A busy table with a few days of history can carry several times its own size in changes. Plan the disk for that, not for the table.

Capture Fewer Columns

CDC captures every column unless you name the ones you need. A table can have two capture instances, so I added a second one that keeps only three columns.

EXEC sys.sp_cdc_enable_table @source_schema = N'dbo', @source_name = N'ShopOrder',
     @role_name = NULL, @capture_instance = N'dbo_ShopOrder_slim',
     @captured_column_list = N'OrderID, Status, Amount';

Then I updated 50,000 more rows, ran the capture and read the sizes again with the query above.

UPDATE dbo.ShopOrder SET Status = 3 WHERE OrderID BETWEEN 100001 AND 150000;
EXEC sys.sp_cdc_scan @maxtrans = 1000000, @maxscans = 100, @continuous = 0;

Both instances added 100,000 rows. The full instance grew by 23.9 MB and the slim one by 10.9 MB. Leaving out the wide Notes column cut the storage by more than half.

Two more results are worth knowing. With only the slim instance enabled, the update still wrote 19.5 MB of log. A shorter column list doesn’t shrink the log. And an update that touched only Notes added zero rows to the slim change table. Changes to columns you don’t capture cost no change rows at all.

Cost 5: Cleanup Is a Big Delete

Old changes don’t vanish on their own. The cleanup job deletes them, and by default it keeps three days. I ran the same cleanup by hand on the full change table.

DECLARE @lsn binary(10) = sys.fn_cdc_get_max_lsn();
EXEC sys.sp_cdc_cleanup_change_table @capture_instance = N'dbo_ShopOrder_all',
     @low_water_mark = @lsn, @threshold = 5000;

Using the newest LSN as the low water mark removes every change the capture has already harvested. It removed 400,000 change rows in about 9 seconds and wrote 211 to 218 MB of log. That’s a delete as large as the capture that created the rows. Right afterward, the change table still reserved 89 to 97 MB for the 100,000 rows it kept. The space comes back later, not the moment cleanup ends.

Retention is a trade. Three days of history on a busy table means three days of change rows on disk. It also means one large delete each day. Keep only what the readers of the change data need.

What I Check Before Turning CDC On

CDC is a good feature. It gives you history without triggers and without touching the application. These checks keep it from surprising you.

• Size the log for bigger update records plus the capture’s own writes. Here, that was more than triple.
• Make sure SQL Agent runs, and alert on the capture job failing or stopping.
• Watch log_reuse_wait_desc. REPLICATION on a database without replication points at CDC.
• Name only the columns the consumers use with @captured_column_list.
• Set the cleanup retention to what the consumers need, not the default by habit.
• Plan disk for the change tables. They can grow larger than the source table.

When you’re done with the test, remove the lab database. Dropping the database removes its capture instances with it.

USE master;
ALTER DATABASE CdcCostLab SET SINGLE_USER WITH ROLLBACK IMMEDIATE;
DROP DATABASE CdcCostLab;

If you’re still choosing between the two built-in options, Change Data Capture or Change Tracking compares them side by side.

Change Data Capture is not free history, it is a second write path you have to watch.

Published by Pinal Dave on SQLAuthority. More of my work at pinaldave.com.