Truncate SQL logs of VBR server?

[Frosty]

We are current running VBR 7 but have been using it since maybe v5. It runs on a VM (Windows Server 2008 R2) and the Veeam SQL database is (I think) a named instance (?) that is also part of the VBR server. In other words, an all-in-one single-VM design.

I was looking at disk usage today and noted that some of the log files (.LDF) associated with this SQL install were fairly large related to the size of the .MDF data files. I concluded that they are not being truncated.

Because we run VBR 7 on a VM all-in-one, when I backup that VM I don't use any VSS 'freeze' because of the belief that it would kill the backup job. So "application aware image processing" is unchecked.

As best I can tell, I cannot access the Veeam SQL database using SQL Management Studio either (at least, I can't using tools installed on my desktop; I have not tried installing management tools on the VBR VM yet).

Suggestions as to how to best manage my VBR SQL database given my setup would be gratefully received.

[foggy]

Because we run VBR 7 on a VM all-in-one, when I backup that VM I don't use any VSS 'freeze' because of the belief that it would kill the backup job. So "application aware image processing" is unchecked.

This is an old and wrong belief, actually, since these days you can backup Veeam B&R server with locally installed SQL database with app-aware image processing enabled without any issues.

Btw, is the only role of this server is running Veeam B&R? I'm asking since in this case you do not even need to back it up as the best way to protect it is using it's built-in configuration backup feature, discussed later in the same topic I've referred to above.

[veremin]

However, even with truncation option enabled, you won't gain any additional space, since SQL truncation operation doesn't reduce actual log size. Thanks.

[Frosty]

Thanks guys, looks like my knowledge hasn't kept up with developments. I will read the links above.

The VM in question is mostly used for Veeam, but also serves several other useful functions. e.g. there are some Scheduled Tasks that it runs overnight as part of the backup regime, it also provides some temp/ad-hoc disc storage. So while it would be pretty easy to recreate/reinstall, a restore from backup would be a lot quicker IMO. I'll investigate the Configuration Backup feature though, as I am pretty sure that's something we are not using yet.

[Frosty]

Well I've both learned and remembered a few things.

Firstly, we actually were doing scheduled Configuration backups on the VBR VM, but since there was no notification method upon completion, it was "out of sight, out of mind" and we just forgot that it was happening.

I've done some testing of the backups of the Veeam VM this morning. Previously we were connecting via the ESXi host instead of vCenter. I can't remember the reason why we originally did that (?) but anyway, I changed it to connect via the vCenter server and it worked just fine. I also ticked the "application aware image processing" box and the backup ran fine. So we should now have transaction-consistent and not just crash-consistent backups. Plus I saw the message about "Truncating transaction logs" appear. So very happy about that!

I guess I will experiment now with installing the SQL Management Tools and see what I can find out about those large log files ...

[Frosty]

Found out why I couldn't remotely connect to the VEEAM SQL database; needed to run the SQL Server Surface Area Configuration tool on the Veaam VM. Once that was sorted I was able to manually run a database Shrink and recover the disk space from the log files. 100% happy now. Thanks!

[Frosty]

Something I noticed just in passing. When I backup this VBR 7 VM, in the list of Actions as the backup runs, I see an action that is "in progress" for the duration of the backup job which says "Waiting for foreign virtual disks to be released". The VBR 7 VM has multiple independent .VMDKs which are not part of the backup job (I only backup C: drive which is SCSI 0:0). I guess that this "foreign virtual disks" action is actually referring to the VMs independent disks, as opposed to disks from backup jobs which might be hot-added. It doesn't stop the job running successfully and as soon as the C: backup is done and theVM snapshot gets removed, everything is fine and the "waiting" action shows as completed. Just mentioning it as it confused me at first. I thought there might have been a backup job which had failed and left a disk hot-added to the VM. But that's not the case.

[veremin]

"Waiting for foreign virtual disks to be released"

This message means that you're backing up machine that acts as proxy server and some other disks are currently hot added to it. More information can be found here.

Regarding independent disks, they don't support snapshots, thus, are automatically skipped from processing during backup/replication jobs.

Thanks.