0

i have always on setup in production environment. Scheduled backup (transaction log backup) already in place every 2 hours using netbackup on secondary database.

The issue i am facing is sometimes the log .ldf file does not truncate. The size is growing until 300gb.

I have troubleshoot on few items as below :

  1. Log reuse desc on that particular database is 'nothing' on both primary and secondary
  2. Log Free space is almost 95% on both primary and secondary
  3. No long running or active transaction on that database on both primary and secondary

Action taken :

  1. took few local transaction log backups. still log not truncated on both primary and secondary

What else i need to check and what is the cause of this issue? Please be remind that this issue only happen sometimes.

2
  • 1
    Did you run DBCC SHRINKFILE (N'Filename_Log' , 10000)
    – SergeyA
    Commented Dec 13, 2023 at 15:25
  • Also, did you examine the VLF layout? Commented Dec 14, 2023 at 10:45

1 Answer 1

1

Log Free space is almost 95% on both primary and secondary

It sounds like the log file IS being truncated. Make sure that you are not confusing truncation of the log with shrinking of the log file.

Running log backups, as you are doing, causes the log to be truncated - meaning that portions of the log file (VLFs) that have been included in a transaction log backup can be reused. This truncation process does not affect the size of the log file.

The way to decrease the size of (shrink) the file is to run DBCC SHRINKFILE.

Please be remind that this issue only happen sometimes.

It sounds like there is likely some process that runs from time to time, and produces so much transaction log activity that the every-two-hours log backups you have set up are not enough to keep the log from growing.

Try to track these instances of log growth to a specific time, and then see what is happening during those times. It could be index rebuilds, some ETL process that bulks loads data, or something else similar to those items.

2
  • thank you so much for the insights....
    – zul nasir
    Commented Dec 15, 2023 at 6:41
  • DBCC SHRINKFILE may not be able to shrink the log to the desired size. Once it hits the currently active part of the log, it stops there. I'm usually able to get around this with a few rounds of CHECKPOINT and log backup. Commented Dec 15, 2023 at 14:47

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.