0

Hope someone can assist me here. I have a DB in mirror mode, i.e. DB on sql1 mirror to sql2 and obviously recovery model is 'Full', and the SQL version is SQL server 2019. Currently, my transaction log file is huge probably because I always run scheduled Database full backups and never ran transaction log backups. I have checked the DB's 'log_reuse_wait_desc' and says LOG_BACKUP. So, I did the Transaction log backup and also made sure 'Truncate the transaction log' was selected. But that didn't reduce the transaction log file size. I took the transaction log back again but this time transaction log file size wasn't reduced immediately, was reduced a few days later. I did this in my lab environment a couple of times. In both instances, when I initiate the transaction log backup, took a few minutes to finish but the transaction log file size was reduced a few days later. I have checked the SQL Server logs & all I can see is log was backed-up. So my questions are,

  1. Why do I have to do twice transaction log backups to reduce transaction log file size?
  2. Why it's taking time to reduce transaction log file size? Is that because of the mirroring & How do I monitor the reduction?

Can you please assist with my queries? Thanks in advance.

Improve this question

1 Answer 1

Reset to default
3

Truncating the Transaction Log doesn't reduce the physical size of the Log files on disk. Rather, it internally marks the space that those files currently consume for re-use so that future transactions can overwrite those bytes in the Log files (instead of growing the files even further - until it runs out of space again).

You would need to run an explicit SHRINK operation as well to release the consumed space by those Log files back to the disk. But typically you only want to do this for one-time events because shrinking is wasteful and is a somewhat heavy operation.

Please see SQL Server Transaction Log Backup, Truncate and Shrink Operations to learn more about the differences.

Because your database uses the Full Recovery Model, your Transaction Log files are going to keep filling up and subsequently growing if you don't take regular Transaction Log backups. This will make your attempt to SHRINK the files useless and wasteful of server resources.

You should either switch to the Simple Recovery Model (which of course would result in you not being able to use Mirroring anymore, but that feature is planned to be deprecated in the future anyway) or take regular Transaction Log backups at an interval conducive to the rate at which transactions occur in your database and that your server is able to tolerate. Some people schedule Transaction Log backups to run ever hour, every 15 minutes, every 5 minutes, or even every 1 minute.

Why it's taking time to reduce transaction log file size?

Without knowing the size of your Log file, and what exact operations and scripts you ran, we can't say much for sure. But again, managing the space and size of the Log files are heavy operations and should only be done appropriately. I'm sure the time it's taking is based on how big you let the Log file grow to.

Improve this answer
3
  • Thanks for the feedback J.D. That's right, I was under the same impression, but when I took the transaction log backup with the truncate option, both times, the log file size decreased from 100 odd GB to MB. I might have to do one time shrink operation to get it under control For now. The log file size was around 100 GB and the typical application operation was nothing much fancy. But the question is why do I have to do transaction log backups twice? As the transaction log file is reducing after backup, is there any way to monitor it?
    – pavk
    Commented Jul 28, 2023 at 10:05
  • Np. "with the truncate option, both times, the log file size decreased from 100 odd GB to MB" - I don't believe that was from truncating, which doesn't release the space back to disk. But maybe you have the Auto-Shrink feature enabled in your database and it was triggered. "But the question is why do I have to do transaction log backups twice?" - You don't. But you do need to take routine Transaction Log backups regularly to avoid growth issues in the future. "...is there any way to monitor it?" - You'd have to roll your own query (e.g. with sp_spaceused) or use a monitoring tool.
    – J.D.
    Commented Jul 28, 2023 at 12:04
  • Checked, auto shrink is not enabled. All I did manual transaction log backup with ‘truncate transaction log’ ‘compress backup’ options. When I took the transaction log back once, the .ldf size didn’t decrease, waited for more than a week. Then I took the transaction log back twice and in the next couple of days, the .ldf file size was decreased. That is where I puzzled, started this thread. The plan was to implement routine transaction log backup often, but trying to find the reason why I had to do it twice, how to monitor it. For monitoring, do you mean SQL activity monitor tool, if so, how?
    – pavk
    Commented Aug 1, 2023 at 4:02

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.