Backup script very slow

Question

Backup script very slow

RVO

SSCertifiable

Points: 5495
More actions
December 18, 2012 at 10:12 am

#268353

I have 5 GB database.
My backup script runs 2 min if I save the file on C drive.
If I run it to save on "\\ networkmachine\bak" it is extremely slow. 10% per 30 min.
I did a test. Saving 1GB file to that network folder takes 40 sec.

Viewing 11 posts - 1 through 10 (of 10 total)

You must be logged in to reply to this topic. Login to reply

Steve Jones - SSC Editor SSC Guru Points: 736212 More actions · Answer 1

A file copy v a backup are two different processes. There's not much you can do to tune the backup process in SQL Server.

Can you show your script? How big is your backup file?

If you copy the backup file to the network, how quickly does it copy?

I recommend backing up locally because if you have network issues, these can cause the backup to fail. It is preferred you back up locally and then copy to the network.

Jeff Moden SSC Guru Points: 1004339 More actions · Answer 2

RVO (12/18/2012)
I have 5 GB database.
My backup script runs 2 min if I save the file on C drive.
If I run it to save on "\\ networkmachine\bak" it is extremely slow. 10% per 30 min.
I did a test. Saving 1GB file to that network folder takes 40 sec.

It could be for any of a dozen reasons. Slow card to the SAN. Slow ethernet cards. Wrong settings o the cards. Slow switches or routers. Slow disk on the far side. Too many hardware hops. Too much external network traffic. The list goes on.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

crazy4sql SSCoach Points: 19590 More actions · Answer 3

My backup script runs 2 min if I save the file on C drive.
If I run it to save on "\\ networkmachine\bak" it is extremely slow. 10% per 30 min.

Dont risk your backup by taking it directly over network.

Take backup on local disk and then copy (using robocopy or some other tool) to network. Also periodically keep deleting the backup from your local disk.

----------
Ashish

RVO SSCertifiable Points: 5495 More actions · Answer 4

I don't have enough space on local drive.

I don't think there is anything with cards or routers.

As I mentioned I did a test. I copied 1 GB file to this network folder and it was fast.

crazy4sql SSCoach Points: 19590 More actions · Answer 5

I don't have enough space on local drive.

better to make space or add separate lun for backups. These days buying storage is not at all costly.

----------
Ashish

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 6

RVO (12/19/2012)
I don't have enough space on local drive.
I don't think there is anything with cards or routers.
As I mentioned I did a test. I copied 1 GB file to this network folder and it was fast.

Two different processes. The backup over the network is nothing like a file copy over the network. Doing this type of comparision is like comparing apples to oranges.

The recommendation is to backup locally, then move the file to a network storage location. Backups directly over the network are susceptible to failure due to the smallest of network issues.

You really should find the space on the local server in your case to accomplish the backup, even if you can't keep it stored there for any length of time. If you aren't using compression, I would look into that as well.

RVO SSCertifiable Points: 5495 More actions · Answer 7

Thanks for valuable advise guys.

Question. How do I estimate the size of Full backup file?

Is it going to be close to the size of data file?

crazy4sql SSCoach Points: 19590 More actions · Answer 8

This article

http://jmkehayias.blogspot.in/2008/12/estimating-size-of-your-database.html

will answer your question.

----------
Ashish

Terry300577 Hall of Fame Points: 3391 More actions · Answer 9

At the place where i used to work this was a method employed by the senior DBA until the length of time the backup took was deemed ridiculous and was taking far longer than expected. One of the DB's in question is now 120GB in size and a solution for this was only put in place when it was taking over half a day to back up this one database, and regularly failed due to network connection issues.

We also had local storage space issues and finding even temporary space for the backups locally was just not possible. we had no rack space for new drives to be added either, so the immediate answer used was someone went and bought a USB hard drive and plugged it in and this was used as a temporary measure until we got a PO signed off for larger capacity drives for the server.

Not elegant at all, but it meant we had backups whereas previously we hadn't had backups especially when network errors had caused the backup to fail.

Maybe look into a temporary solution for the immediate future and look into some capacity planning for the longer term future allowing you time to get the relevant funds acquired and changes made.

RVO SSCertifiable Points: 5495 More actions · Answer 10

Crazy4SQL - THANKS A LOT!

http://jmkehayias.blogspot.in is a very good source.

Straight to the point, works perfectly.

I like this kind if technical websites.