April 18, 2008 at 1:31 pm
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%.
April 18, 2008 at 1:59 pm
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.
😎
April 21, 2008 at 5:47 pm
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.
April 21, 2008 at 6:38 pm
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).
April 21, 2008 at 6:45 pm
Sorry about that. I meant RAID 1.
Thanks for the suggestions. I guess the short answer is, "No. SQL Server cannot throttle a backup."
I have asked our hosting provider about options for a new single physical disk.
April 21, 2008 at 6:50 pm
Sorry - I never answered that part!
No, not as far as I am aware of!
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply