SQL Server Log File Full at 2 TB Max with CDC: How We Recovered

Quick summary

For several versions of SQL Server, the maximum log file size was noted at 2 TB. If you ever reach that limit you could find yourself in a Disaster Recovery scenario.

Context

We received an emergency call from a client that noted that their SQL Server instances was unresponsive. (This was an Amazon RDS instance, although that didn’t play much into the ultimate root cause.) The client had some technical staff already looking at the issue, and when we joined the call we were informed that the transaction log for their main production database was completely full, and all transactional activity in the database had stopped.

For many versions, SQL Server has had a default maximum files size of 2 TB on transaction log files. Even though newer versions like SQL Server 2022 and 2025 may say “unlimited”, they aren’t truly unlimited. The current documentation notes that transaction log files still have a 2 TB maximum file size.

Investigation: Why log_reuse_wait_desc Showed REPLICATION

After a quick review, we confirmed that the transaction log for their main production database was completely full, and had reached the maximum size of 2 TB. The log reuse description in sys.databases showed “REPLICATION” as the cause for the log being full.

This instance did not have replication, but the client did have Change Data Capture (CDC) enabled on numerous tables, which also shows up as “REPLICATION” in the lof reuse information. We discovered the CDC capture and cleanup jobs had not been running for weeks, which caused the transaction log to hold transactions and not mark them for reuse.

Unfortunately, before we joined the call, the client had enabled CDC thinking that would “clean up the log”, but what that did in the immediate moment was fill up the log faster as it generated more transactions to update data in the CDC support tables.

So now the file was already at the 2 TB limit, meaning it couldn’t be grown further. But here’s the biggest problem: any other solution to reduce the log file size at this point would not work because…it would involve a logged transaction. And the log was full.

Possible solutions include:

• Disable CDC – that’s a logged transaction
• Add another log file – that’s also a logged transaction
• Declare it a disaster and restore the entire database from the most recent backups

The fix – kill another transaction and add another log file

We wanted to avoid restoring the database from backup, as that could mean further hours of downtime as well as more lost transactions. We determined the best possible solution was to try to Kill one of the smaller queries using the transaction log, and then quickly add another log file. It took several tries, but eventually we were able to kill one and add a second transaction log file, which allowed other transactions to start completing again.

Something like this:

BEGIN TRANSACTION;

/* Kill session 99 to release any locks/log space it may be holding */

 KILL 99;

/* Add a second log file to DatabaseName on a different drive */

ALTER DATABASE [DatabaseName]

ADD LOG FILE (

NAME = N'DatabaseName_log2'

, FILENAME = N'L:\ Logs\DatabaseName_log2.ldf'

, SIZE = 64MB

, MAXSIZE = 2TB

, FILEGROWTH = 64MB);

 COMMIT TRANSACTION;

After we had the second log file we were able to monitor the progress for CDC and the drive space used by all the transactions recorded. This took a few hours but eventually caught up, and afterwards we emptied and removed the extra transaction log file and resized the original log file to reduce the massive amount of Virtual Log Files (VLFs). We also added a job to alert us in case the transaction log became more than 50% full to avoid another occurrence of this issue.

We were very fortunate, because had we not found a tiny windows to add another log file we would have had no choice but restore from backups. While you’re reading this, make sure your backups are actually ready for restores – check out our free tool, sp_CheckBackup to make sure.