I was working with a client recently who ran into a problem with one database, using the simple recovery model, where the log was growing and would not clear. They checked the log_reuse_wait_desc field in sys.databases and it said REPLICATION.

They had previously set up the database as a replication subscriber but they’d since removed replication, and yet the log still would not clear when they ran a checkpoint (which is what attempts to clear the log when using the simple recovery model).

They had tried the usual things: running sp_repldone, running sp_removedbreplication, and even re-configuring and removing replication again. Nothing worked.

When they told me this, my response was to ask if they were using Change Data Capture in that database. And they were.

What they didn't know is that Change Data Capture harvests committed transactions on the tables being monitored from the transaction log. And it does this using the replication Log Reader Agent job if replication is configured, or its own capture job if replication is not configured. One of their developers had written custom logic around the call to sp_cdc_scan in the capture job and had made a mistake that prevented the scan from starting, so the log just grew and grew.

But why did the log_reuse_wait_desc say REPLICATION and not CDC? Because there is no separate indication that CDC is preventing the log from clearing – it uses the REPLICATION value, which can cause a lot of confusion!

Bottom line: If you ever see a log_reuse_wait_desc value of REPLICATION, and replication isn’t configured, check the is_cdc_enabled flag in sys.databases as well, and then troubleshoot CDC from there