Long running backup time

Question

Post reply

Long running backup time

~paul hewitt

SSCommitted

Points: 1933
More actions
December 3, 2008 at 4:59 am

#198431

This morning I have been asked to backj up an externally developed SQL database which has expanded to 147GB. I currently have it in simple recovery mode, and am have been running the backup for 2 hours.
Could I please ask - how long would you anticipate a backup of this size to run for, in your experience? The server in question here is old and ran out of disk space so may be struggling. I have added 300GB temporary extra storage this morning to run the backup.
thanks
Paul

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

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

CrazyMan SSChampion Points: 12560 More actions · Answer 1

Hi Paul

It depends, are u backing to a network folder? is the machine been used by any other process??

~paul hewitt SSCommitted Points: 1933 More actions · Answer 2

Its backing up locally from one hard drive to another. There is some other activity on the server but it is very light. I am only seeing about 1-3% cpu utilization which is a bit worrying.

CrazyMan SSChampion Points: 12560 More actions · Answer 3

I am having a 164 GB Database which is been copied across network to a storage location, it takes 6 hours for me to complete the backup, i dint even look into that until now, but iam using Redgate Backup with compression

~paul hewitt SSCommitted Points: 1933 More actions · Answer 4

Thank you, that is reassuring. I will wait patiently for a few more hours then.

Much obliged,

Paul..

CrazyMan SSChampion Points: 12560 More actions · Answer 5

lets know how long does your backup take, just interested in timings

~paul hewitt SSCommitted Points: 1933 More actions · Answer 6

Well, I left it running when I went home last night. Just arrived this morning, and the backup is about half way through, after maybe 23 hours!

I knew the kit was a bit underspecified, but... crikey!

Jerry Hung SSChampion Points: 12968 More actions · Answer 7

23 hours seemed excessive for 147GB

You should try backing up to LOCAL drive first (if space allows), do a Verify Backup as well

then use some copy utilities (RoboCoyp, TotalCopy, TeraCopy) to copy to network locations (100Mbps or Gigabit could dramatically affect that)

SQLServerNewbieMCITP: Database Administrator SQL Server 2005

~paul hewitt SSCommitted Points: 1933 More actions · Answer 8

Thanks Jerry.

I did run on a local drive but it was a slow external disk - all I had available at the time. I think allocating more than 1gb memory to SQL Server would have helped.

Not to worry, the backup is finished now. I have identified several gb of obsolete tables within the database which I have dropped, and am now shrinking it.

cheers for your help

Paul

EdVassie SSC Guru Points: 60476 More actions · Answer 9

Try specifying multiple output files for the backup. This should allow parallelism of the database read so that all files are filled at the same time.

I once had a SQL Litespeed backup of a 300GB database that was set to back up to 4 output files that ran in about 90 minutes. One day when setting up a new DR site I set it to backup to a single file and it took 6 hours. Resetting it to 4 files put it back to 90 minutes again.

Original author: https://github.com/SQL-FineBuild/Common/wiki/ 1-click install and best practice configuration of SQL Server 2019, 2017 2016, 2014, 2012, 2008 R2, 2008 and 2005.

When I give food to the poor they call me a saint. When I ask why they are poor they call me a communist - Archbishop Hélder Câmara