Note: If your Hyper-V host is running Windows Server 2016/2019 and your SQL VM is running on a Windows Server 2012R2 (or older) operating system, please note that there is a confirmed Microsoft bug. VMs running Windows Server 2012R2 (or older) will not be able to truncate the logs in this case and since Windows Server 2012R2 is in extended support, Microsoft does not release non-security updates. Their recommendation in this case is to update the VM's Operating System to Windows Server 2016 (or newer) in order to avoid such supportability issues.

Should you wish to confirm this with Microsoft, you may feel free to contact them and reference Microsoft Support Case number 118101819243072.


PROBLEM


When running SQL 2005 / 2008 / 2008 R2 / 2012 inside a guest VM, the Microsoft SQL Transaction Logs are not being truncated after an Altaro backup succeeds.



CAUSE


Since Altaro uses Microsoft Shadow Copy tasks to backup VMs, it will only trigger a Database Backup, not a Database Log Backup, thus transaction logs will only be truncated when Altaro runs a backup if you select the Simple Recovery Model for your SQL databases.


More information on SQL recovery models can be found here: https://msdn.microsoft.com/en-us/library/ms189275.aspx



SOLUTION


Simple recovery model


Under a Simple Recovery Model, SQL will truncate the transaction logs when a Shadow copy is taken. This happens when Altaro requests a Shadow Copy from the MS Hyper-V VSS writer, this then cascades the shadow copy task into the guest VM and the MS SQL VSS writer will kick off a Database backup, This process will truncate the transaction logs, assuming of course that the SQL VSS writer is installed and enabled.


Full or Bulk Logged recovery model


Under the Full or Bulk Recovery Model the transaction logs are not truncated until a Database Log Backup has been completed. An Altaro backup is not considered a Database Log Backup.  If the Full or Bulk Recovery Model is required, using the Microsoft SQL Management Studio to schedule a Transaction Log Backup Task will be required to maintain the Transaction Log size.


Note: This is not a best practice on how to set up a SQL server but an explanation of how Altaro interacts with SQL and its different recovery models.