1

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?

3
  • "Because of this the growing LOG file issue, I've disabled the CDC on the database...This didn't have the desired effect - the used space in log is still growing." - Sounds like CDC likely is unrelated then. Monitor your database for running queries for a day and see what comes up. A formal monitoring tool would be ideal, but you can log the results of sp_WhoIsActive to a table with a SQL Agent Job too. It's fairly lightweight, and can be ran every few minutes on a not so busy server, so schedule the job accordingly. Report back when you find something suspect.
    – J.D.
    Dec 15, 2022 at 13:38
  • Hi J.D.! Thank you for your answer. This issue has started after the CDC was enabled. so, i'm assuming there must be a "connection" with CDC... is there a way to see what is insice the transaction log, e.g. what are the transaction are are not released?
    – saso
    Dec 15, 2022 at 13:44
  • If your Transaction Log backups are running, and there are no open transactions, then there are no transactions that are "not released". The space in the log file is marked for re-use. Your best bet is to find what's running on your server, which the tool I mentioned can tell you.
    – J.D.
    Dec 15, 2022 at 14:17

1 Answer 1

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 (?).

1
  • Good to know, but what did you end up doing as a solution ? Oct 7, 2023 at 13:34

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.