SQL Periodic Log Backup with 200+ databases

[lightsout]

Hey all,

So I've got periodic log backups set-up hourly for my SP 2010 server running on SQL 2008 R2.

As I've got 200+ content databases, this is causing Veeam to struggle. I'm getting this message:

"Transaction log backup too longer than desired log backup interval"

So I changed the default 15 minute interval to 60 minutes, but still the warning occurs.

I am just pushing Veeam too hard? Or is this something I should get support involved with?

Thanks!

[Vitaliy S.]

How long does it take to backup database log files? Can you take a look at the stats?

[foggy]

Depending on the server performance and amount of changes occurred inside databases transaction logs backup of such amount of DBs can indeed take considerable time.

[lightsout]

So:

1. It seems to be taking about 2hrs 30 mins to process. I'll see if I change the periodic timeout to 3hrs, if the warning stop.
2. Is there any chance of it getting faster? Not sure where the bottleneck is, as the transaction, logs are not huge (average size is 235MB according to Veeam).

[Vitaliy S.]

I'm not aware about any ways of increasing the performance. 200+ databases for 2 hours and 30 min for backup and all preparations seems to be a pretty good result.

[lightsout]

Thanks for the input. I'll try it at 3hrs and see how it does.

[Vitaliy S.]

Try to allocate more CPU/Memory resources to the SQL Server, might help as well.

[lightsout]

Which one? The transaction log source, or the Veeam instance which is doing the backup?

[Vitaliy S.]

The transaction log source. Veeam instance does not do any job, it just stores configuration data.

[Gostev]

Actual transaction log backup is performed by the SQL server itself (native functionality), Veeam just triggers one - and then collects the resulting file.

[lightsout]

I'll have to try that, as the periodic log backup can't be made longer than an hour. Darn!

[lightsout]

More resources has helped, but still not enough. I'll keep adding more but I think I'll get diminishing returns.

Is there anyway to increase the periodic backup frequency beyond 60 minutes?

[veremin]

Nope, you can't perform SQL log backup on less frequent basis. Thanks.

[P_R_S]

So:

1. It seems to be taking about 2hrs 30 mins to process. I'll see if I change the periodic timeout to 3hrs, if the warning stop.
2. Is there any chance of it getting faster? Not sure where the bottleneck is, as the transaction, logs are not huge (average size is 235MB according to Veeam).

Are the DBs all on the same server?

[foggy]

Judging on the original post, yes. Otherwise, the first recommendation would be to split the job into several ones.

[P_R_S]

Judging on the original post, yes. Otherwise, the first recommendation would be to split the job into several ones.

yess... so the second recommendation is to distribute the databases among several servers... (three should be enough)

[foggy]

Yes. This, however, intends environmental changes rather than just re-configuration of the backup jobs.

[lightsout]

Yes, all on one server. Not my design to be honest. So we consolidated them (they were SharePoint sites so this was quite straight forward) into 40 DBs, and the job now runs in approx 30 mins, so we can do transaction log backups effectively!