SQL Server Transaction Log Backups and Log File Size

0
votes

We use SQL Server 2012 (SP2) running in Full recovery mode. We take half-hourly transaction log backups. Our DB size is 20GB. Every night we perform DB maintenance tasks - full backup, rebuilding some indexes, update statistics, clearing some old data. The half-hourly transaction log backups save to an external HDD. Our transaction log LDF file consistently grows to around 13GB. The transaction log backup .trn files are around 20-30MB (depending on activity of DB of course), but the one immediately after the nightly maintenance tasks is 13GB. My understanding was that the transaction log LDF file would automatically shrink back to 0 immediately after a transaction log backup. However, it seems like it expands to the maximum size it needs and stays that size.

Is this normal? Is it a problem? Is there any way to automatically shrink it to 0 after the transaction log backup? (I know how to shrink it manually).

Thanks, Jon W

1
sql-serverloggingtransactions
You shouldn't shrink the log-file especially to 0. It's senseless because it will grow until it gets the size it needs. And log-file increasing is an expensive enough operation that should be avoided in production enviroment if it's possible. - Denis Rubashkin
Agree about not shrinking to 0 - I meant more to a reasonable size, sorry - Jon Weir

1 Answers

0
votes

the one immediately after the nightly maintenance tasks is 13GB.

When you perform index rebuild in full model, the size of your log file grows at least to the size of that index. When you rebuild 13Gb of indexes, all the 13Gb go to the log file. This is because every page of a new index is written to the log as it is.

My understanding was that the transaction log LDF file would automatically shrink back to 0 immediately after a transaction log backup

This is wrong. Log backup does not perform shrink, i.e. the size of log file is not changed after log backup.

Log truncation that log backup perfoms has nothing to do with the size of log file. It only means that some part of log file is no more retained and can be overwritten by new log records.