0

I am using Azure Backup to take a transaction log backup from my SQL 2016 Enterprise VM every 15 mins. It also takes one full backup daily. The db is using Full recovery model.

Problem is that the Azure Backup solution is not shrinking the log file after the transaction log backups. Is there any way to automate this process or ensure that the log file doesn't grow out of control?

I'm using this Azure Backup solution: https://docs.microsoft.com/en-us/azure/backup/backup-sql-server-database-azure-vms#create-a-backup-policy

1

I think there may be some confusion here on the word "Shrinking" - The log file is not ever "Shrunk" by a backup. It can be "truncated" by the log backup, but not shrunk.

When a log is truncated, the segments in the log that are no longer needed for recovery (either because you are in SIMPLE recovery model and a checkpoint has ensured that the data is in the data file securely, or you are in FULL recovery model and the log records have been backed up with a transaction log backup) are effectively marked for overwrite. The log is allowed to use the space those "old/unneeded" log records consumed because they aren't required for recovery.

Note I didn't say "Shrink". The truncation means you can possibly continue writing to the space already allocated to the log file - if there is enough. But truncation will NEVER shrink a log file. It just means it may not have to grow more.

Truncation is more or less an internal log file maintenance type operation. I love what they used to call SIMPLE recovery model back in SQL Server 2000 - "Truncate on checkpoint" mode.

Shrinking is a physical operation on the file itself. I have a few opinions on shrinking data and log files in a rather long answer here. But, in general, don't fear a bigger file. Fear an out of control file that is growing unconstrained. If you are truly taking log file backups, and SQL is showing that you have - you likely just have a file with mostly empty space - meaning the file can be rewritten, and will. It's rather healthy to have a file that is "right-sized" to not have to shrink/grow and shrink/grow between log backups.

  • It's probably not so appropriate a picture - but I used to describe this to coworkers a decade ago in fat loss terms. Imagine surgery to remove fat (liposuction?) as truncation - the 'internal space' is gone. But the skin is still the same size. So a second surgery to tighten the skin would be "shrinking". The log backup simply truncates, it does the inside "stuff", the shrink is what changes the actual .ldf file size. You may or may not want to do that - based on VLFs/etc - the link goes further. – Mike Walsh Oct 7 '19 at 22:11
  • Haha, one of the weirder analogies. I like it. :-) – Tibor Karaszi Oct 8 '19 at 6:40
  • @MikeWalsh thanks for clarifying - indeed, the file is being truncated but I assumed that shrinking was necessary. However, after monitoring for a while, I see that the log file size remains consistent (~30 GB) but does not grow, so I assume it's just re-using the empty space as needed. Thanks! – blizz Oct 8 '19 at 17:17

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.