Skip to main content

Transaction logs on Ms Sql Server Hyper-V guests

Thread needs solution
Beginner
Posts: 4
Comments: 6

Hi,

Is someone able to confirm that when we are using the Hyper-V agent on a Hyper-V host to back up a guest running MsSql server, that any database in "Full" recovery mode has it's transaction logs backed up and/or truncated?

We have a MsSql DB server on which we keep getting the transaction log space fill up.

During the backup I get a message about "Fast Incremental/Differential" not being available on the REFS volume but the Creation of the partition image and the overall backup is reported as successful. There seems no other level of logs for the backup.

I have performed restores to a VM for the guest. I just need to know the extent to which the backup is managing the transaction logs.

Thanks,

Albert

0 Users found this helpful
Support specialist
Posts: 0
Comments: 469

Hello Albert.

Is someone able to confirm that when we are using the Hyper-V agent on a Hyper-V host to back up a guest running MsSql server, that any database in "Full" recovery mode has it's transaction logs backed up and/or truncated?

It depends on whether a Log truncation option  is enabled: the SQL transaction logs are truncated after each successful backup.

We have a MsSql DB server on which we keep getting the transaction log space fill up.

I suggest that you open a case with Acronis Support Team to investigate this issue.

During the backup I get a message about "Fast Incremental/Differential" not being available on the REFS volume but the Creation of the partition image and the overall backup is reported as successful.

According to ReFS support limitation in Acronis Backup 12.5, files cannot be excluded from a disk backup. Since with the "Fast Incremental/Differential" option enabled the program scans the files in the disk-level backup by file properties, this option is also limited on ReFS volumes.

I have performed restores to a VM for the guest. I just need to know the extent to which the backup is managing the transaction logs.

Each incremental backup contains the SQL log plus those data changes (in your MsSql DB) that have been performed since the previous incremental backup and other associated files. Thus, this VM is restored into a state it was when its snapshot (with the help of VSS) has been created

Beginner
Posts: 4
Comments: 6

Thanks Maria,

I was hoping someone would have the knowledge to answer the questions without me having to submit yet another support ticket.

This is an "Entire machine" backup. The listed options does not include "Log truncation" so does it use our system default setting - currently "Enabled"?

The VSS for Virtual Machines setting is enabled so transactions on all VSS aware applications should occur. Truncation needs to occur or the transaction log file space will fill. The "sys.databases" view for sql server shows the "log_resuse_wait" property is "2" - waiting for a "LOG_BACKUP".

 

Support specialist
Posts: 0
Comments: 469

Hello Albert.

 

This is an "Entire machine" backup. The listed options does not include "Log truncation" so does it use our system default setting - currently "Enabled"?

If this option is not available, then it is "Disabled". This option is effective for backup of Microsoft SQL Server databases and for disk-level backup with enabled Microsoft SQL Server application backup. Thus, you need to have Agent for SQL to be installed on the machine running Microsoft SQL Server. 

The VSS for Virtual Machines setting is enabled so transactions on all VSS aware applications should occur. Truncation needs to occur or the transaction log file space will fill. The "sys.databases" view for sql server shows the "log_resuse_wait" property is "2" - waiting for a "LOG_BACKUP"

We recommend that you use Application-aware backup option.

Beginner
Posts: 4
Comments: 6

Hi Maria,

The "Application Backup" setting appears for plans for physical devices but not for plans for virtual devices in my Acronis Backup environment.

We are running Hyper-V.

The "VSS for Virtual Machines" I understood would be enough to backup VSS aware applications.

That's the indication in the

In the "Application-aware backup" section of the documentation you refer, I found:

On a virtual machine, no agent installation is required; it is presumed that the machine is backed up by Agent for VMware (Windows).

I understood Hyper-V was fully supported by Acronis Backup.

For the transaction logs, you are saying that with a Hyper-V host, backing up (as an entire machine) a Windows server with VSS options containing a Microsoft SQL Server which is VSS aware, Acronis Backup doesn't cater for the truncation of the transaction logs.

I guess I'll have to raise a ticket to ask this question.

Thanks and cheers,

Albert

 

Support specialist
Posts: 0
Comments: 469

Hello Albert.

In the "Application-aware backup" section of the documentation you refer, I found:

On a virtual machine, no agent installation is required; it is presumed that the machine is backed up by Agent for VMware (Windows).

This works as described on ESXi virtual machines (you can find this reference here in the first row). As for Hyper-V VMs, you should install the Agent for SQL inside the Hyper-V VM, as I advised you above.

The support of application-aware backup for Hyper-V VMs is planned to be added in future releases (ABR-82709).