SQL backup performance issues

[dbr]

Dear all,

By default Veeam backs up SQL transaction log every 15 minutes once it is enabled in guest processing settings. We noticed the following when backing up SQL servers periodically:

1. Every time the SQL backup a new backup interval starts the corresponding metadata is retrieved from the Veeam database even though the metadata is also stored on the repository. I opened a ticket and the engineers said this is currently by design and will be changed in one of the future versions. In our current configuration about 40MB is tranfered over wan per backed up SQL vm. This doesn't sound much but 40MB per SQL vm, 5 vms per site results in 200MB traffic every 15 Minutes on wan link with 5Mbit for example. The link is saturated 5 minutes every 15 minutes. Currently the SQL log backup retention is configured to keep all log backups until the corresponding image-level backup is deleted.

2. On a remote site 4 vms are backed up with 67 databases in total and 17 vm restore points in the primary backup chain (and 30 restore points in copy repository). The SQL restore point count is 202430 in the backup file.

3. Every 15 minutes our Veeam database, which is currently located on a shared storage, takes a third (5000) of all iops (15000) and a half (150) of throughput (300).

Q1. Any ideas how to reduce the amount of data sent via wan and iops on the shared storage? Will a change in SQL log backup retention take the wanted effect?

Q2. Does anyone know how the number of SQL log backups is calculated? 4 times an hour x 24 hours a day x 67 databases x 17 restore points results in 109344 but the SQL log backup count in the backup (Backups -> Disk -> Job-Name -> SQL Server transaction log backup) is 202430.

Greets, Daniel.

[foggy]

Hi Daniel, are you backing up directly to a remote repository? In future logs will be supported by backup copy jobs so you will be able to back them up locally first. As for the number of restore points, then this is most likely explained by this fact:

Currently the SQL log backup retention is configured to keep all log backups until the corresponding image-level backup is deleted.

Log backups in your case are stored according to the parent job retention, which, in case forward incremental mode is used, has more than 17 restore points on disk, depending on the week day.

[dbr]

Hi Alex,

to explain our environment: Veeam backup Server in main site and SQL vm, Veeam proxy with repository in remote site. The problem was, that additional to the metadata on the repository a xml list of sql oib was sent from the Veeam backup server respectively Veeam backup db server to the proxy server (case#02326051 / forum post). To me it didn't make sense because all metadata should already present on the repository.

I guess the high amount of data being sent via wan is most likely caused by the high number of in database stored SQL restore point metadata. So my idea was to decrease the sql backup retention down to 2 days which I've set this morning. After restarting some jobs it looks like there's definitely less data being sent over wan and the number of entries in the table Backup.Model.SqlOIBs decrased from about 1.5 million to 800.000. For many sites the number of SQL backup under Backups -> Disk -> Job-Name -> SQL Server transaction log backup went down except of a few sites. Especially the example site was decreased only from 202430 to 178682. I'm afraid there are many orphaned entries. Is there a way to check orphaned SQL logs (SQLOibs) or a way to cleanup SQL backup log data manually?

[foggy]

I wouldn't recommend doing this without our engineers' assistance.

[dbr]

You mean open a case?

[foggy]

If you want to cleanup the database, yes.

[dbr]

For anyone who is interested I want to share some experience:

There were definitely some backups in the database for which the corresponding files didn't exist anymore. The problem wasn't the table sqloibs but the table storages in which were entries related to non-existing vlb files. Because I couldn't wait for support, I stopped all jobs, deleted all sql backups and let them created from scratch with a retention of 2 days. Deleting the backups also cleaned up all old vlb entries in table storage. If anyone encounter the same problem one may fire up this statement to check for vlb backups in the database prior to a given date and time:

Code: Select all

SELECT * FROM [VeeamBackup].[dbo].[Backup.Model.Storages]
where creation_time <=  convert (datetime,'2018-11-20 23:59:21.783') and file_path like '%.vlb'
order by creation_time

After deleting all sql backups manually via gui, no database cleanup by support was necessary.

[dbr]

It seems that the total number of sql restore points per job is calculated as follows:

(number of databases vm1 + number of databases vm2 + number of databases vmn) x cycles per hour x 24 hours a day x number of days (retention) = total number of restore points

Example:

(vm1 with 3 dbs + vm2 with 120 dbs + vm3 with 4 dbs + vm4 with 20 dbs) x 4 cycles per hour x 24 hours x 2 days = 28224 restore points

Additionally I noticed that deleting a backup with a high number of sql restore points takes much longer than without sql restores points, so please be patient .