Delay between backups on SQL Backup Pro

edited February 19, 2016 6:34AM in SQL Backup Previous Versions
I see a delay in my backup logs of five minutes. I have 150 small databases to backup. The actual backup takes less than a minute, bu then it's another five minutes between when the next backup begins. Why is this?

I setup a job on another one of my servers that has a similar number of databases, and there is no delay between backups.

The log isn't giving me any insight in why this would be.

Thanks!
Ike Ellis

Comments

  • peteypetey Posts: 2,358 New member
    Could you please post the backup command you are using to back up your databases? Thanks.
    Peter Yeoh
    SQL Backup Consultant Developer
    Associate, Yohz Software
    Beyond compression - SQL Backup goodies under the hood, updated for version 8
  • I opened a ticket. I don't want to make you do repeated work. I'll post the results of the ticket here for Google.
    Ike Ellis
  • peteypetey Posts: 2,358 New member
    If it helps, the most common cause of the delay is when you:
      - backup a lot of databases using the BACKUP DATABASES syntax - to the same folder - and use the ERASEFILES option to delete older backup files
    In these situations, there will be a lot of files in that single folder, and after every database backup, SQL Backup will scan every single file in that folder to determine which file it needs to delete. This issue will be compounded if you back up to a network share, as the scanning process will take even longer.

    What you can do is to place each database's backup files in its own separate folder using the <DATABASE> tag e.g. instead of
    EXEC master..sqlbackup '-sql "BACKUP DATABASES &#91;*&#93; TO DISK = &#91;\\netshare\backups\&lt;AUTO&gt;&#93;... " '
    
    use
    EXEC master..sqlbackup '-sql "BACKUP DATABASES &#91;*&#93; TO DISK = &#91;\\netshare\backups\&lt;DATABASE&gt;\&lt;AUTO&gt;&#93;... " '
    
    Now, SQL Backup needs to scan only that specific database's folder to determine which file(s) to delete, which speeds things up significantly. If that is still too slow, you can add another subfolder based on the backup type using the <TYPE> tag e.g.
    EXEC master..sqlbackup '-sql "BACKUP DATABASES &#91;*&#93; TO DISK = &#91;\\netshare\backups\&lt;DATABASE&gt;\&lt;TYPE&gt;\&lt;AUTO&gt;&#93;... " '
    
    Peter Yeoh
    SQL Backup Consultant Developer
    Associate, Yohz Software
    Beyond compression - SQL Backup goodies under the hood, updated for version 8
  • petey wrote:
    If it helps, the most common cause of the delay is when you:
      - backup a lot of databases using the BACKUP DATABASES syntax - to the same folder - and use the ERASEFILES option to delete older backup files
    In these situations, there will be a lot of files in that single folder, and after every database backup, SQL Backup will scan every single file in that folder to determine which file it needs to delete. This issue will be compounded if you back up to a network share, as the scanning process will take even longer.

    What you can do is to place each database's backup files in its own separate folder using the <DATABASE> tag e.g. instead of
    EXEC master..sqlbackup '-sql "BACKUP DATABASES &#91;*&#93; TO DISK = &#91;\\netshare\backups\&lt;AUTO&gt;&#93;... " '
    
    use
    EXEC master..sqlbackup '-sql "BACKUP DATABASES &#91;*&#93; TO DISK = &#91;\\netshare\backups\&lt;DATABASE&gt;\&lt;AUTO&gt;&#93;... " '
    
    Now, SQL Backup needs to scan only that specific database's folder to determine which file(s) to delete, which speeds things up significantly. If that is still too slow, you can add another subfolder based on the backup type using the <TYPE> tag e.g.
    EXEC master..sqlbackup '-sql "BACKUP DATABASES &#91;*&#93; TO DISK = &#91;\\netshare\backups\&lt;DATABASE&gt;\&lt;TYPE&gt;\&lt;AUTO&gt;&#93;... " '
    

    Brilliant post Petey. With over 400 databases things were taking forever. Now I have them all in their own folders. Thanks for saving me a lot of time.
    I think this phen375 review is amazing.
Sign In or Register to comment.