Full/Differential Backups extremely slow

tknoob · January 18, 2013 2:32PM

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

petey · January 18, 2013 7:48PM

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

Thanks.

tknoob · January 19, 2013 12:12AM

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

petey · January 19, 2013 12:59AM

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.

EXEC master..sqlbackup '-sql "BACKUP ... TO DISK = &#91;\\prod.ent\proddfs\DBBackupFolders\backup_sql_db07\Backup_SQL\&lt;DATABASE&gt;\&lt;AUTO&gt;.sqb&#93; ...

In this way, SQL Backup only needs to read the header of 4 files each time, to determine which backup sets to delete.

tknoob · January 19, 2013 8:59PM

Thanks Peter.

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

Thanks.

Tim

tknoob · January 23, 2013 3:21PM

Hi Peter,

Worked great. Thanks.

Tim

Full/Differential Backups extremely slow

Comments

Product Learning

Community Forums

Events & Friends

Simple Talk