Red Gate forums :: View topic - Full/Differential Backups extremely slow
Return to www.red-gate.com RSS Feed Available

Search  | Usergroups |  Profile |  Messages |  Log in  Register 
Go to product documentation
SQL Backup 7
SQL Backup 7 forum

Full/Differential Backups extremely slow

Search in SQL Backup 7 forum
Post new topic   Reply to topic
Jump to:  
Author Message
tknoob



Joined: 07 Jun 2006
Posts: 6

PostPosted: Fri Jan 18, 2013 8:32 pm    Post subject: Full/Differential Backups extremely slow Reply with quote

Hi,

Our Full and Differential backups have become extremely slow.
Backing up over 1400 databases takes over 12 hours or longer.
Using SQL Backup 7.2.1.4.

Does SQL Backup start choking with a large number of databases in an Instance?

Thanks.

Tim
Back to top
View user's profile Send private message
petey



Joined: 24 Apr 2005
Posts: 2299

PostPosted: Sat Jan 19, 2013 1:48 am    Post subject: Reply with quote

Could you please post the entire backup command you are using?

Thanks.
_________________
Peter Yeoh
SQL Backup Consultant Developer
Associate, Yohz Software
Beyond compression - SQL Backup goodies under the hood, updated for version 7
Back to top
View user's profile Send private message Send e-mail
tknoob



Joined: 07 Jun 2006
Posts: 6

PostPosted: Sat Jan 19, 2013 6:12 am    Post subject: Reply with quote

Here's the backup command:

EXECUTE master..sqlbackup '-SQL "BACKUP DATABASES EXCLUDE [tempdb] TO DISK = ''\\prod.ent\proddfs\DBBackupFolders\backup_sql_db07\Backup_SQL\<AUTO>.sqb'' WITH ERASEFILES = 3b, MAILTO_ONERRORONLY = ''sqlalerts@bbbbb.com'', CHECKSUM, DISKRETRYINTERVAL = 30, DISKRETRYCOUNT = 10, COMPRESSION = 4"'

Thanks for your help.

Tim
Back to top
View user's profile Send private message
petey



Joined: 24 Apr 2005
Posts: 2299

PostPosted: Sat Jan 19, 2013 6:59 am    Post subject: Reply with quote

The bottleneck is probably the ERASEFILES option. You could check if that is the case by omitting this option once and see if the backup completes faster.

If it is indeed the ERASEFILES option that's causing the slowdown, it's because:

1. the backups for all 1400 databases are stored in the same folder, on a network share. As there are 3 backup sets of each database at any one time, that's 4200 files that SQL Backup needs to check every time it backs up a database, to determine which backup file to delete. It needs to read the header of each file to match the database and backup type.

2. the files are stored on a network share. That makes reading the header of 4200 files, 1400 times (once for each database), even slower.

What you could try doing is to separate each database's backup file into their own folder using the <DATABASE> tag e.g.

Code:
EXEC master..sqlbackup '-sql "BACKUP ... TO DISK = [\\prod.ent\proddfs\DBBackupFolders\backup_sql_db07\Backup_SQL\<DATABASE>\<AUTO>.sqb] ...

In this way, SQL Backup only needs to read the header of 4 files each time, to determine which backup sets to delete.
_________________
Peter Yeoh
SQL Backup Consultant Developer
Associate, Yohz Software
Beyond compression - SQL Backup goodies under the hood, updated for version 7
Back to top
View user's profile Send private message Send e-mail
tknoob



Joined: 07 Jun 2006
Posts: 6

PostPosted: Sun Jan 20, 2013 2:59 am    Post subject: Reply with quote

Thanks Peter.

Good idea. I'll let you know how it goes.

Thanks.


Tim
Back to top
View user's profile Send private message
tknoob



Joined: 07 Jun 2006
Posts: 6

PostPosted: Wed Jan 23, 2013 9:21 pm    Post subject: Reply with quote

Hi Peter,

Worked great. Thanks.

Tim
Back to top
View user's profile Send private message
Display posts from previous:   
Reply to topic All times are GMT + 1 Hour
Page 1 of 1

 
You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum
You cannot vote in polls in this forum


Powered by phpBB © 2001, 2005 phpBB Group