Long running full backup in secondary does not allow log truncation in primary replica

Question

Hi,

I have a 2 node WSFC and I have synchronous AG with automatic failover.

My log file keeps on growing despite frequent successful log backups and dbcc sqlperf('logspace') shows 99.9% of space is used and the log is not getting truncated.

There are no long running open transactions transactions in my primary and we use secondary only to offload backup workload.

log*_ reuse_ wait _ desc* shows AVAILABILITY_REPLICA.My secondary replica is fully synchronized and log_send_queue and redo_log_queue size are normal.

I can only see a full backup in progress in my secondary which is running for more than 2 days.

Does a long running active full backup in secondary replica does not allow truncation of logs because sql engine needs the log information from the start of the full backup operation for successful completion of a consistent backup

Answer 1

Hi udhayan,

Welcome to Q&A Forum, this is a great place to get support, answers and tips.

Thank you for posting your query, I'll be more than glad to help you out.

First I would recommend to troubleshoot why your full backup is running that long! If your database is that big then you should optimize the backup command with different options to speed up the process. If your database size is not that big... you might have a slow storage or having other problems causing the backup process not to finish.

From my understanding, after a successful log backup (after the full) everything should work as expected...

I hope my answer is helpful to you,

Your

Bjoern Peters

If the reply was helpful, please upvote and/or accept it as an answer, as this helps others in the community with similar questions. Thanks!

Answer 2

Hi @udhayan d ,

Does a long running active full backup in secondary replica does not allow truncation of logs because sql engine needs the log information from the start of the full backup operation for successful completion of a consistent backup

Yes. A long running active full backup in a secondary replica can prevent log truncation on the primary replica. When a full backup is running on a secondary replica, the log records for the databases in the backup chain are held until the full backup completes.

The log file on the primary replica is not getting truncated. The log_reuse_wait_desc column in sys.databases is showing "AVAILABILITY_REPLICA" in your case which indicates that the secondary replica is currently holding onto log records.

Alternatively, you can try running a differential backup on the primary replica, which can capture all the changes made to the database since the last full backup. This can help to reduce the size of the log file and free up space for log truncation.

I am not familiar with AWS. There may be some performance issues. Maybe you need investigate why the full backup on the secondary replica has been running for so long.

Best regards,

Seeya

---

If the answer is the right solution, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".