Full/Differential Backups extremely slow

tknoob

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

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

Thanks.

tknoob

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

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 = [\\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.

tknoob

Thanks Peter.

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

Thanks.


Tim

tknoob

Hi Peter,

Worked great. Thanks.

Tim