December 14, 2005 at 11:17 am
I recently moved my backups from the wizard generated maintenance plans to a simple backup script. My 180GB database is now taking 7 hours to backup with instead of the normal 4 hours. The step details show 7.359 MB/sec when the backup completes in 7 hours and 12.453 MB/sec when the backup completes in 4 hours.
Below is my script. This is actually step 2. Step 1 deletes the previous backup.
BACKUP DATABASE MyDB
TO DISK = '\\MyNAS\sql\server\MyDB\MyDB.bak'
WITH INIT
During the backup, pinging the NAS device from the SQL Server takes 0ms.
I have used Perfmon and the counters seem usual from what I have researched (e.g., http://www.sql-server-performance.com/backup_restore_tuning.asp).
Average Device Throughput bytes/sec: 5570346
Average % Disk Time: 3.854
Average Disk Queue Length: .0193
Average IO Write Bytes/sec: 5897638
Average Split IO/sec: 0
As for sp_who2, the only thing peculiar is a DISKIO of 875659 for SQLAgent - Alert Engine.
No compression is taking place on either machine.
Does anyone have any suggestions as what is causing the backup time to almost double?
Thanks,
ray
SS2K
December 15, 2005 at 2:01 am
Maybe the network is very busy on that hours. Measure the counters regarding your network.
7M/sec seems very slow
I have a 170GB database that takes 106 minutes to backup.
December 15, 2005 at 2:07 am
Hello Ray,
I don't know the answer to your question, but even the shorter time is quite long... did you consider the possibility to use some application that makes the backup run faster? We are using Litespeed on a DB of the same size (almost 200 GB), and it only takes 1 hour. At the same time, the backup file is considerably smaller than with a normal backup - slightly over 30 GB.
BTW, the posted path suggests that backups could be located on the same disk as the database file. If it is so, I'd try to do the backup on a separate disk. It still doesn't explain the difference between the two backup times - I hope someone will be able to help you with it, unfortunately I have no idea why this should happen.
December 16, 2005 at 11:19 am
We had a 160 Gb database that was backed up through a maintenance plan and it executed in 65 minutes. We have since modified the the backup into 'stripes' (4 .BAK files instead of 1) and the execution time dropped to 31 minutes.
RegardsRudy KomacsarSenior Database Administrator"Ave Caesar! - Morituri te salutamus."
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply