Comprehensive data protection for all workloads
Post Reply
lightsout
Expert
Posts: 227
Liked: 62 times
Joined: Apr 10, 2014 4:13 pm
Contact:

SQL Periodic Log Backup with 200+ databases

Post by 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.
VP, Product Management
Posts: 27055
Liked: 2710 times
Joined: Mar 30, 2009 9:13 am
Full Name: Vitaliy Safarov
Contact:

Re: SQL Periodic Log Backup with 200+ databases

Post by Vitaliy S. »

How long does it take to backup database log files? Can you take a look at the stats?
foggy
Veeam Software
Posts: 21069
Liked: 2115 times
Joined: Jul 11, 2011 10:22 am
Full Name: Alexander Fogelson
Contact:

Re: SQL Periodic Log Backup with 200+ databases

Post by 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
Expert
Posts: 227
Liked: 62 times
Joined: Apr 10, 2014 4:13 pm
Contact:

Re: SQL Periodic Log Backup with 200+ databases

Post by 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.
VP, Product Management
Posts: 27055
Liked: 2710 times
Joined: Mar 30, 2009 9:13 am
Full Name: Vitaliy Safarov
Contact:

Re: SQL Periodic Log Backup with 200+ databases

Post by 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
Expert
Posts: 227
Liked: 62 times
Joined: Apr 10, 2014 4:13 pm
Contact:

Re: SQL Periodic Log Backup with 200+ databases

Post by lightsout »

Thanks for the input. I'll try it at 3hrs and see how it does.
Vitaliy S.
VP, Product Management
Posts: 27055
Liked: 2710 times
Joined: Mar 30, 2009 9:13 am
Full Name: Vitaliy Safarov
Contact:

Re: SQL Periodic Log Backup with 200+ databases

Post by Vitaliy S. »

Try to allocate more CPU/Memory resources to the SQL Server, might help as well.
lightsout
Expert
Posts: 227
Liked: 62 times
Joined: Apr 10, 2014 4:13 pm
Contact:

Re: SQL Periodic Log Backup with 200+ databases

Post by lightsout »

Which one? The transaction log source, or the Veeam instance which is doing the backup?
Vitaliy S.
VP, Product Management
Posts: 27055
Liked: 2710 times
Joined: Mar 30, 2009 9:13 am
Full Name: Vitaliy Safarov
Contact:

Re: SQL Periodic Log Backup with 200+ databases

Post by Vitaliy S. »

The transaction log source. Veeam instance does not do any job, it just stores configuration data.
Gostev
Chief Product Officer
Posts: 31460
Liked: 6648 times
Joined: Jan 01, 2006 1:01 am
Location: Baar, Switzerland
Contact:

Re: SQL Periodic Log Backup with 200+ databases

Post by 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
Expert
Posts: 227
Liked: 62 times
Joined: Apr 10, 2014 4:13 pm
Contact:

Re: SQL Periodic Log Backup with 200+ databases

Post by lightsout »

I'll have to try that, as the periodic log backup can't be made longer than an hour. Darn!
lightsout
Expert
Posts: 227
Liked: 62 times
Joined: Apr 10, 2014 4:13 pm
Contact:

Re: SQL Periodic Log Backup with 200+ databases

Post by 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
Product Manager
Posts: 20271
Liked: 2252 times
Joined: Oct 26, 2012 3:28 pm
Full Name: Vladimir Eremin
Contact:

Re: SQL Periodic Log Backup with 200+ databases

Post by veremin »

Nope, you can't perform SQL log backup on less frequent basis. Thanks.
P_R_S
Novice
Posts: 5
Liked: never
Joined: Jan 17, 2012 7:19 pm
Full Name: Riccardo Perni
Contact:

Re: SQL Periodic Log Backup with 200+ databases

Post by P_R_S »

lightsout wrote: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
Veeam Software
Posts: 21069
Liked: 2115 times
Joined: Jul 11, 2011 10:22 am
Full Name: Alexander Fogelson
Contact:

Re: SQL Periodic Log Backup with 200+ databases

Post by foggy »

Judging on the original post, yes. Otherwise, the first recommendation would be to split the job into several ones.
P_R_S
Novice
Posts: 5
Liked: never
Joined: Jan 17, 2012 7:19 pm
Full Name: Riccardo Perni
Contact:

Re: SQL Periodic Log Backup with 200+ databases

Post by P_R_S »

foggy wrote:Judging on the original post, yes. Otherwise, the first recommendation would be to split the job into several ones.
:D yess... so the second recommendation is to distribute the databases among several servers... (three should be enough)
foggy
Veeam Software
Posts: 21069
Liked: 2115 times
Joined: Jul 11, 2011 10:22 am
Full Name: Alexander Fogelson
Contact:

Re: SQL Periodic Log Backup with 200+ databases

Post by foggy »

Yes. ;) This, however, intends environmental changes rather than just re-configuration of the backup jobs.
lightsout
Expert
Posts: 227
Liked: 62 times
Joined: Apr 10, 2014 4:13 pm
Contact:

Re: SQL Periodic Log Backup with 200+ databases

Post by 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!
Post Reply

Who is online

Users browsing this forum: No registered users and 149 guests