Backup is slow with multiple files

rowlettron

I am testing the backup on a test database that is 300GB and lives on a SQL 2005 cluster. When I backup with just one file, the backup is very quick. When I backup with 5 files on 5 different disk resources that are independent of each other, the time for the backup more than triples in duration. Any ideas?

petey

Please see http://www.sqlbackuprestore.com/speedingupbackups.htm. Throughput also depends on where your database files are located, not just where the backup files are located. Also see the 'Optimizing backup speed' topic in the SQL Backup help file, which offers some guidance on how to measure and improve your backup throughput.

rowlettron

I tried the link but it returns page not found. Where the data file is located should not make a difference since backing up to a single file goes very fast. But to provide insight on the data file, it is located on an independent LUN that does not share any disks with the backup LUNs.

petey

Please see http://www.sqlbackuprestore.com/speedingupbackups.htm

and also the 'Optimizing backup speed' topic in the SQL Backup help file. It should help you in determining where's the bottleneck when you back up to 5 files.

rowlettron

I looked at your page and I have already configured the database as suggested. It is only when I backup to multiple files using the tool that it slows down. I have done a native sql backup to a single file and multiple files. In both cases, throughput is great. I have backed up to a single file using the tool and throughput is great.

Disk speed - RAID 10 on a SAN. Database is broken up into 12 files with each file being on a separate independent LUN with no shared disks.
Database files - See bullet above.
Really use physically different disks - See first bullet
File fragmentation - Less than 5%
Transaction log - Stored on a separate independent LUN on the SAN
Backup files - Each one is stored on a different independent LUN.

petey

Could you please post some numbers on the speed you are seeing, for both native backups and SQL Backup backups, for backups to 1, 2, 3, 4 and 5 files? Would like to see the increase/decrease for both backup types, as you move from 1 file to 5 files.

Thanks.