Full/Differential Backups extremely slow

Compresses, encrypts, secures and monitors SQL Server backups.

Moderators: JonathanWatts, Chris Auckland, eddie davis, Colin Millerchip, Brian Harris, james.billings, RBA, petey

Full/Differential Backups extremely slow

Postby tknoob » Fri Jan 18, 2013 8:32 pm

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
tknoob
 
Posts: 6
Joined: Wed Jun 07, 2006 11:54 pm

Postby petey » Sat Jan 19, 2013 1:48 am

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 7
petey
 
Posts: 2233
Joined: Sun Apr 24, 2005 12:34 pm

Postby tknoob » Sat Jan 19, 2013 6:12 am

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
tknoob
 
Posts: 6
Joined: Wed Jun 07, 2006 11:54 pm

Postby petey » Sat Jan 19, 2013 6:59 am

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.

Code: Select all
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.
Peter Yeoh
SQL Backup Consultant Developer
Associate, Yohz Software
Beyond compression - SQL Backup goodies under the hood, updated for version 7
petey
 
Posts: 2233
Joined: Sun Apr 24, 2005 12:34 pm

Postby tknoob » Sun Jan 20, 2013 2:59 am

Thanks Peter.

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

Thanks.


Tim
tknoob
 
Posts: 6
Joined: Wed Jun 07, 2006 11:54 pm

Postby tknoob » Wed Jan 23, 2013 9:21 pm

Hi Peter,

Worked great. Thanks.

Tim
tknoob
 
Posts: 6
Joined: Wed Jun 07, 2006 11:54 pm


Return to SQL Backup 7

Who is online

Users browsing this forum: No registered users and 1 guest