long backup duration

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.co...ore_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
Hi,
Was your Wizard generated backup maintenance plan overwriting the same
backup file each time or writing to a new file each time? The only
thing I can think of is that if the backup file already exists and you
are re-initializing it takes less time. From an OS/Network standpoint
that should not hinder performance. Perhaps you can look at the wizard
generated backup plan to see if there are some option enabled by SQL
Server which may increase performance.
Shahryar
raybouk wrote:

>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.co...ore_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
>
Shahryar G. Hashemi | Sr. DBA Consultant
InfoSpace, Inc.
601 108th Ave NE | Suite 1200 | Bellevue, WA 98004 USA
Mobile +1 206.459.6203 | Office +1 425.201.8853 | Fax +1 425.201.6150
shashem@.infospace.com | www.infospaceinc.com
This e-mail and any attachments may contain confidential information that is legally privileged. The information is solely for the use of the intended recipient(s); any disclosure, copying, distribution, or other use of this information is strictly prohi
bited. If you have received this e-mail in error, please notify the sender by return e-mail and delete this message. Thank you.
|||Delete the old backup file prior to performing the backup. Dont init the new
backup device. I found this to work the best.
On a sidenote, ping rates do not necessarily reflect thoughput performance.
"raybouk" wrote:

> 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.co...ore_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
|||What was the destination of the Wizard generated backups?
There is going to be a world of difference between writing to a local disk
vs going to a NAS device.
You should be able to go into the Management Studio and look at the script
that it generates.
See what the differences are in the backup statement.
"Raoul Laoyan" <RaoulLaoyan@.discussions.microsoft.com> wrote in message
news:0C4A91B3-D06B-40DA-87EE-1D94A94A707F@.microsoft.com...[vbcol=seagreen]
> Delete the old backup file prior to performing the backup. Dont init the
> new
> backup device. I found this to work the best.
> On a sidenote, ping rates do not necessarily reflect thoughput
> performance.
> "raybouk" wrote: