Full/Differential Backups extremely slow

tknoobtknoob Posts: 9
edited January 23, 2013 3:21PM in SQL Backup Previous Versions
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

Comments

  • peteypetey Posts: 2,358 New member
    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 8
  • 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
  • peteypetey Posts: 2,358 New member
    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.
    Peter Yeoh
    SQL Backup Consultant Developer
    Associate, Yohz Software
    Beyond compression - SQL Backup goodies under the hood, updated for version 8
  • Thanks Peter.

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

    Thanks.


    Tim
  • Hi Peter,

    Worked great. Thanks.

    Tim
Sign In or Register to comment.