Red Gate forums :: View topic - Delay between backups on SQL Backup Pro
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

Delay between backups on SQL Backup Pro

Search in SQL Backup 7 forum
Post new topic   Reply to topic
Jump to:  
Author Message
ike@ikeellis.com



Joined: 13 Aug 2013
Posts: 2
Location: San Diego, CA

PostPosted: Wed Mar 19, 2014 11:54 pm    Post subject: Delay between backups on SQL Backup Pro Reply with quote

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
Back to top
View user's profile Send private message
petey



Joined: 24 Apr 2005
Posts: 2305

PostPosted: Thu Mar 20, 2014 10:28 am    Post subject: Reply with quote

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 7
Back to top
View user's profile Send private message Send e-mail
ike@ikeellis.com



Joined: 13 Aug 2013
Posts: 2
Location: San Diego, CA

PostPosted: Thu Mar 20, 2014 10:57 pm    Post subject: Reply with quote

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
Back to top
View user's profile Send private message
petey



Joined: 24 Apr 2005
Posts: 2305

PostPosted: Fri Mar 21, 2014 2:28 am    Post subject: Reply with quote

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

Code:
EXEC master..sqlbackup '-sql "BACKUP DATABASES [*] TO DISK = [\\netshare\backups\<AUTO>]... " '

use
Code:
EXEC master..sqlbackup '-sql "BACKUP DATABASES [*] TO DISK = [\\netshare\backups\<DATABASE>\<AUTO>]... " '

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.

Code:
EXEC master..sqlbackup '-sql "BACKUP DATABASES [*] TO DISK = [\\netshare\backups\<DATABASE>\<TYPE>\<AUTO>]... " '

_________________
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
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