Throttle Backup

Question

Post reply

Throttle Backup

kevin77

SSCarpal Tunnel

Points: 4041
More actions
April 18, 2008 at 1:31 pm

#185562

Is there a way to throttle a database backup? Everybody says to do a "local" backup first, then copy those files else where. The problem is, we only have one logical disk drive on the database server (two physical in RAID 1) and it appears that when the backup job is running, the entire server is pretty unresponsive. Simple database requests from our application time out.

Our database is 33 GB in size and it takes about 40-50 minutes to do the backup. Is there a way to throttle the backup job so that it doesn't thrash the disk. I don't care if it takes 2 or 3 hours to complete. The server is a dual, dual-core with 4 GB of RAM. CPU utilization during the backup generally isn't very high. Sometimes there will be spikes of 50 to 60%.

Viewing 6 posts - 1 through 5 (of 5 total)

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

Lynn Pettis SSC Guru Points: 442447 More actions · Answer 1

I don't know of any way to throttle the backup process, but I would recommend looking into software that can compress your backups as the backup runs.

I am currently evaluating HyperBac from HyperBac, and what I have seen so far is looking good. I found out about this product from SQL Server Magazine. In our Data Warehouse development environment, HyperBac has reduced the size of our disk backup files 80% (I can save 5 files in space of 1 native backup), and backup times have been reduced 50%.

We are looking at purchasing this product, and I look forward to deploying it in several of current production environments.

😎

Steve T SSCommitted Points: 1994 More actions · Answer 2

kevin77 (4/18/2008)

Is there a way to throttle a database backup? Everybody says to do a "local" backup first, then copy those files else where. The problem is, we only have one logical disk drive on the database server (two physical in RAID 5) and it appears that when the backup job is running, the entire server is pretty unresponsive. Simple database requests from our application time out.

Our database is 33 GB in size and it takes about 40-50 minutes to do the backup. Is there a way to throttle the backup job so that it doesn't thrash the disk. I don't care if it takes 2 or 3 hours to complete. The server is a dual, dual-core with 4 GB of RAM. CPU utilization during the backup generally isn't very high. Sometimes there will be spikes of 50 to 60%.

I don't think compression will help you here. By adding compression, you are adding CPU utilization on top of your disk utilization. The normal database backup doesn't take a lot of CPU utilization, I remember it scales depending on the system resources.

I think attacking the underlying disk problem is really your only option unless physical dimensions of the server bar this route. It sounds like you probably have huge disk queue lengths, causing disk thrashing and so forth.

Toby Harman SSCarpal Tunnel Points: 4159 More actions · Answer 3

Are you sure about your disc config - Two disks in RAID 5 doesn't sound right to me!

I have to agree that the problem you are seeing is disc queue times. Try really hard to get yourself an extra physical disc and write the backup files to that. If you are writing the backups to tape after then this disc doesn't even need RAID (though it would be preferrable).