SQL Server DB Log keeps growing / CDC enabled database

Question

I've enabled change data capture on a SQL Server database.

The database is in full recovery mode.

The LOG backups of the database are being done hourly.

The capture jobs was running without any issues - the number of the transactions was not very large - sys.dm_cdc_log_scan_sessions was returning empty_scan_count = 1 occasionally.

I've noticed that the log file is growing and is not being released. The used space in log file went from around 3GB to 9GB in one day and was growing (the space was not being released).

DBCC OPENTRAN returns no open transactions.

Also the log_reuse_wait_desc from sys.databases is returning NOTHING.

Because of this the growing LOG file issue, I've disabled the CDC on the database (sys.sp_cdc_disable_db).

This didn't have the desired effect - the used space in log is still growing.

What could be the reason and how to solve it?

---

I'm starting to suspect one issue:

I wanted to enable a table for the CDC (sys.sp_cdc_enable_table). However, as the table was locked by a process with the "Sch-S" lock set, the enable table didn't go through and the other queries started getting stuck. I killed the session that was executing the sys.sp_cdc_enable_table. The was no sign of anything being done (not CDC table, table not listed in CDC enabled tables, etc.). Is it possible that this has caused the issue?

Answer 1

I did a more extended research yesterday. Long story short: This is expected behaviour and is by design so and has nothing to do with CDC.

There was a similar case described here: https://learn.microsoft.com/en-us/answers/questions/281466/transaction-log-noticeable-used-space-after-restor.html (last answer from user "amomen")

I've then read the Microsoft guide on transaction log architecture: https://learn.microsoft.com/en-us/sql/relational-databases/sql-server-transaction-log-architecture-and-management-guide?view=sql-server-ver15

Basically, my expectation was that the used space of log would be reduced after each transaction log backup. This is not the case. SQL server uses up the space of the VLF and empties it only when it gets full. The screenshots in the last post on the first link (learn.microsoft.com) show this. Other issue is that the VLF size itself seems to get enlarged in some scenarios. I assume this happened when I've enabled CDC for a large amount of tables --> this then enlarged the number of transactions in the database and SQL Server enlarged the VLF size (?).