Backup is slow with multiple files

rowlettronrowlettron Posts: 4
edited May 1, 2012 11:07AM in SQL Backup Previous Versions
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?
· Share on Twitter

Comments

  • peteypetey Posts: 2,358 New member
    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.
    Peter Yeoh
    SQL Backup Consultant Developer
    Associate, Yohz Software
    Beyond compression - SQL Backup goodies under the hood, updated for version 8
    · Share on Twitter
  • rowlettronrowlettron Posts: 4
    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.
    · Share on Twitter
  • peteypetey Posts: 2,358 New member
    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.
    Peter Yeoh
    SQL Backup Consultant Developer
    Associate, Yohz Software
    Beyond compression - SQL Backup goodies under the hood, updated for version 8
    · Share on Twitter
  • rowlettronrowlettron Posts: 4
    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.
    · Share on Twitter
  • peteypetey Posts: 2,358 New member
    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.
    Peter Yeoh
    SQL Backup Consultant Developer
    Associate, Yohz Software
    Beyond compression - SQL Backup goodies under the hood, updated for version 8
    · Share on Twitter
Sign In or Register to comment.