1

I have a 2 node WSFC and I have SQL Server 2017 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

Improve this question
1
  • "a full backup in progress in my secondary which is running for more than 2 days." - How big is the database it's backing up?
    – J.D.
    Commented Feb 16, 2023 at 20:48

1 Answer 1

Reset to default
3

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

Ouch, that's excessive.

Does a long running active full backup in secondary replica does not allow truncation of logs [...]

Correct, log truncation is not possible in the middle of a backup.

Improve this answer
1
  • It was a litespeed backup which never completed. stuck at 100% for long and had to be killed. After that log truncated in primary replica
    – udhayan dharmalingam
    Commented Feb 17, 2023 at 10:14

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.