Feature Request - SQL Log Backups

[bsmall244]

After enabling SQL transaction log backups in Veeam instead of using SQL backup for our production SQL servers, we have discovered an issue with the way that the backups are processed on the source VM. It seems that Veeam streams the transaction log backup file to a temporary location on the C: drive of the source VM (C:\ProgramData\Veeam\Backup\SqlLogBackup), and then copies that file over to the backup repository. This almost caused a major issue with our production server, as the C: drive ran out of space, which caused the log backups to fail several times over a weekend, and in turn caused the transaction logs in SQL to swell and almost run that drive out of space as well. Our servers are very busy over the weekends and the transaction logs can swell to over 200GB in a day. It seems to me that there is an unnecessary duplication of data that happens when Veeam runs a backup to a temporary location, then copies that data to the repository. I know that a SQL Maintenance Plan can stream the backup file to a network drive, so why can't Veeam do the same thing and stream the backup file directly to the repository rather than placing it in a temporary location on the C: drive? It seems that this is a very dangerous method, as filling up the C: drive can crash the server. There has got to be a way to avoid duplicating the transaction log data on the source VM before copying it to the repository. I know that it is required to run a SQL transaction log backup to some location in order for this process to work, but it seems to me that someone can put some more thought to this and find a way to stream that file directly to the repository. Placing the temporary backup locally on the source VM is dangerous, and defaulting to the C: drive is even more so!

[Gostev]

Hi, where do you suggest we collect the SQL log backup data before backup process is completed? 200 GB is obviously too much to hold in RAM. We cannot stream directly to repository, because it may not be always available, resulting in missed RPOs.

[mkretzer]

I also think bsmall244 has a point here and i do not understand what Gostev is saying about RPOs.

In case of SQL logs if we back them up at a later time we can still restore every possible time in the logfile. And if the repo is not available it gets not backed up anyway. So in this situation we already have a problem somewhere (repo not available) and now Veeam causes a much bigger issue by filling up the SQL server drive.
Why should RPO be our biggest concern in this situation?? We had this issue and our SQL admins were NOT HAPPY.

One tip for bsmall244: What helped in our environment was to globally enable SQL server compression on the SQL servers. It might lead to a slightly increased CPU usage but it helped us keep the log backups small enough. It would be nice if Veeam could make this an option so we would not have to do this globally on the SQL.

Markus

[johannesk]

I agree with bsmall244 and mkretzer. We have had problems just like those described by bsmall244 - and it takes down the credibility of the product among the DBA's.

I like the suggestions to be able to make veeam compress the log files. Also if it would be possible (and perhaps it is) to change the default log backup location to another drive than drive C:\.

I don't understand the argument of repository not being available. I would not like drive C: to be filled up waiting for a repository to go online. You would run your repository server to be highly available anyway.

[Gostev]

With the proposed architecture, if a repository goes down, Veeam will not be able to take log backups at all. This in turn means that SQL Server transaction logs will continue growing all this time (because they can only be truncated after successful backups), and will eventually overfill their volume, crashing SQL server. And you cannot restore it to the latest state because you have no log backups anywhere (unlike with the current approach) => missed RPO.

So ideally, we need to combine both approaches together somehow. But that's a lot of additional complexity which we like to avoid, because it directly impacts reliability. And this complexity also means we won't be able to add this functionality in a short time frame.

Also if it would be possible (and perhaps it is) to change the default log backup location to another drive than drive C:\

Yes, temporary log backup location is configurable.

From the issues described above, it sounds like quick and dirty fix might be for us to be automatically selecting a volume with most free disk space for temporary log backup location, instead of defaulting to %TEMP% location?

[johannesk]

One tip for bsmall244: What helped in our environment was to globally enable SQL server compression on the SQL servers. ...... It would be nice if Veeam could make this an option so we would not have to do this globally on the SQL.

Markus

Does that work - If compression is enabled globally on the SQL, will the files sent to the by veem logshipper to "C:\ProgramData\Veeam\Backup\SqlLogBackup" be compressed?

[Gostev]

To add to that, after some research and discussion with the devs we've decided to add an option to use compression with native log backups to further reduce the disk space they take. Unfortunately though, this functionality seems to require SQL Server Enterprise edition.

[mkretzer]

Does that work - If compression is enabled globally on the SQL, will the files sent to the by veem logshipper to "C:\ProgramData\Veeam\Backup\SqlLogBackup" be compressed?

The info came initially from Veeam support. Case 01006690. I am 99 % sure it works as since we enabled it globally on all SQL servers we have less problems with the log backup disk space.

[Harry.E]

Same issue here. C: drive 100% full due to Veeam transaction log backup. I would recommend to update the documentation:
(A) Emphasize that the logs are extracted from the DB and written temporary to the VM AND to make ensure that sufficient space is available to hold all transaction logs !!
(B) Clearly specify the directory where the temporary logs are stored in the documentation.

For us the solution was to map a separate disk with 100GB into this path.

[leonmahler]

Hi Bsmall244,

How quickly does your log back-up grow? Is it 200GB per 30 minutes or is it 200GB per 24 hours?

Besides of the fact that Veeam copies the logs into the memory/ %TEMP% before a back-up/truncate, maybe you can create a schedule to run log backups every 4/2/1/0,5 hour(s)? This should reduce the risk op missing your RPO like Gostev mentioned and helps to achieve RTO.

This is how I configured an environment of 1400 Databases (653 Instances, 168 servers) which can grow really quick as well. I also configured alerts in SCOM so our Servicedesk can monitor the health status of the environment more proactively.

[CPCS]

Hi,

please veeam fix this!

At least and first it would be nice if the temp path would be selectable - but of course i would like to not waste a second partition with the same amount of space like the log files...Second update the documentation please, so that others know about this BEFORE running into this! This is the second time (first is/was that SQL restore is performed on local SQL express on the backup server and therefore space have to be available and not exceeding the 10gb express limit i think) i RANDOMLY STUMBLED over a major problem which can cause you serious problems which is not documented.

Is the compression not available in the Standard editions? If not, this "solution" would not even be applicable to all users...

[cousinsp]

As a SQL DBA, I think it would be best to backup the tran logs to the default backup location as set in SQL Server. I believe this location is stored in the registry.

[mkretzer]

The problem is that if you backup more often then the maximum time Veeam allows the backup to take reduces. If you backup every 15 minutes the max time the backup may take is one hour. That was an issue in our environment because some tasks lead to ~ 100 GB of logfile in a few minutes.

Gesendet von meinem Nexus 6P mit Tapatalk

[PAF]

Suggestion 1: Veeam should check if there is enough space on the C drive before using it, so avoid filling it and possibly crashing the SQL server. If there is not enough space the backup job should fail before writing to C.
Suggestion 2: If there is not enough space at C, Veeam could check if any other drive have enough space. SQL servers often have separate drives for db and db-log. WIndows Installer seems to use such a method.
Suggestion 3: Alternativly, Veeam should not use any local drive at all, but a shared folder at one of the Veeam servers.

[PAF]

Suggestion: The Veeam agent should enable the NTFS compression flag on the folder C:\ProgramData\Veeam\Backup\SqlLogBackup. The .bak files from SQL Server can often be compressed 50-75%.
Of course the flag can be enabled manually as well.

[CPCS]

I dont have the C:\ProgramData\Veeam\Backup\SqlLogBackup Folder on my SQL Server which is backuped? oO
Instead there is an empty Oracle folder?! oO

[foggy]

Make sure you're actually back up SQL transaction logs and the folder is not redirected to another location.

[Buh95]

Hi all,

We're studying the impact of migrating a big SQLServer (ERP/High transactional profile) from a physical server to a virtual one (Several TB of databases).

We've been happy Veeam users for years now, and we are already using Veeam to backup some small SQLServer Vmware VM, with periodic log backup (every hour).

All is working perfectly, but in case of our big sqlserver, I would have somme reserves, regarding the capacity of veeam to backup huge amount of logs.

In Veeam, the process of log backup uses a temporary local disk space to copy log files, before sending them to the veeam proxy server.
Today, we configure this temporary local disk space to redirect to another local disk (SQLTempLogPath in registry).

Our big sqlserver is able to generate between 1GB and 30GB per hour (in normal condition), and even 100GB/200GB of logs when DBA launch special REORGANIZE index operations...

So what would happen to my Veeam Log Backup :
=> Several long minutes to duplicate locally all the logs
=> and others several long minutes to send them by the network to the veeam server.

Does someone already experiment such a case in production ?
Do you know if there is a way to bypass the local log copy ?

Thanks in advance for your help.

[foggy]

Thanks for the feedback, currently there's no way to bypass the local copy of log files. You can find some thoughts on this above.