Smart Log backups SQL Server 2017

Smart log backups is a feature introduced in SQL 2017 to allow DBAs to manage the transaction log growth better. Before we go into details it might be better to understand what problem it was trying to solve.

In any typical production workload we often have uneven usage on the database system, often the majority of the work is performed during office hours so we see the T-log grow significantly during this time and in order to control the disk usage we take frequent backups. However during non-peak hours we see that taking such frequent backups is an overhead since there are hardly any changed being logged in the transaction log file when no users are connected.

Sometimes due to sudden spikes in usage we can also see the TLOG grow significantly before the next backup run is scheduled as a result it grows to a larger size before the log backup can be taken unfortunately this excess size is not reclaimed automatically after the backup completes resulting in wasted disk space.

I have seen a few implementations where DBAs have used this feature to reduce the number of T log backups taken during off peaks hours and I don’t agree with the approach. Here is why

https://www.youtube.com/watch?v=hkKI3KTiOvs