Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

We've had reports of queries running slowly or timing out early in the morning, and the only job I see running that I think could affect this is our database backup job.

The database itself is about 300GB, and the backup job starts at 4:30am and doesn't finish until a little after 7:00am. The current syntax of our backup job is:

BACKUP DATABASE [DatabaseName]
TO DISK = N'E:\Database Backups\DatabaseName.Bak'
WITH INIT, NOUNLOAD, NAME = N'DatabaseName.Bak',
NOSKIP, STATS = 10, NOFORMAT

E:\ is a partition on the server which holds both the databases and the database backups.

It should also probably be noted that this is a virtual server, not a dedicated standalone server. We started getting complaints about slowdowns during the backup process right after we switched to a virtual server, so I think it may be related.

Is there a way to run this backup job so it doesn't affect the query performance while its running?

We are using SQL Server 2005

share|improve this question

3 Answers 3

up vote 11 down vote accepted
  1. Isolate backup I/O from the rest of the I/O on your system. While it may take longer and be more prone to hiccups, backing up over the network instead of to a local disk may help alleviate the direct impact on the instance. Even in a virtual machine you should be able to expose other storage so that SQL Server can write to a different I/O subsystem.
  2. It will be unlikely to make any difference whatsoever but, if this is run from a job, why do you need to use the STATS option? Are you sure you need the other options (NOUNLOAD, NOSKIP, NOFORMAT)? I haven't done any extensive performance testing on the entire matrix of options, but IMHO you should only use the options that you know you need.
  3. Run your full backups at a different time that doesn't interfere with regular user activity. This isn't always possible, but even in 24/7 operations there are peak and lull times.
  4. If you move to 2008+ you can take advantage of backup compression (Enterprise on 2008, Standard or Enterprise in 2008 R2+). If you can't move to 2008 or don't have the adequate edition, there are 3rd party backup tools that will do compression for you, and they're all pretty good at it. Since 2008 I've been able to use native compression, and I have been quite satisfied with both speed and compression % there with no need to further invest in 3rd party. But with 2005 I remember having good success with Red-Gate SQL Backup; Quest LiteSpeed is also quite good, but I have no idea what kind of effort is being put into it since the Dell acquisition.
  5. If your databases are in full recovery, you could go longer between full backups, trading that for the knowledge that if you have to recover to a point in time, you will potentially have more logs to restore. You could combine this option with Ali's suggestion below of taking diffs throughout the week - if you had to recover on Thursday, you would need to restore 1 full, 1 diff, and the logs since the diff.
  6. If you have a lot of data that is stale and unchanging, you could consider hosting that in a different database with a different recovery plan, or at least on different filegroups, and split up your backup operations that way. If you have reference or archive data that can be made read-only, and if you are in simple recovery, you can move it to its own read-only filegroup, back it up once, and not have to include it in your nightly backups ever again. See these MSDN topics:

Performing piecemeal restores

Example: Piecemeal Restore of Only Some Filegroups (Simple Recovery Model)

share|improve this answer
    
Would you be able to point me in the right direction for learning more about isolating backup I/O from the rest of the I/O in the system? #3 (changing time of backups) isn't an option since 4:30am is the best time for running the backup, and we've talked about upgrading to 2008 (#4), but I don't think that will be happening anytime soon. –  Rachel Dec 18 '12 at 19:07
    
Not sure how to point you in the right direction - can you expose other storage to the virtual machine? –  Aaron Bertrand Dec 18 '12 at 19:11
    
Yes, I think we can expose other storage to the virtual machine. When you say "isolating backup I/O from the rest of the I/O in system", are you referring to backing up to a network drive instead of a disk drive? –  Rachel Dec 18 '12 at 19:15
2  
No, I mean backing up to any other I/O subsystem other than the disk where you're also simultaneously performing reads and writes for regular database and tempdb activity. This could be a different disk drive on the same server, a share on another machine, a different LUN on the SAN, a share on a NAS, etc. –  Aaron Bertrand Dec 18 '12 at 19:17
    
I The piecemeal restore is a great solution that I didn't think of, thanks for posting it! –  Ali Razeghi Dec 18 '12 at 19:19

Use third-party backup software (I would recommend EMS SQL Backup) which can backup databases 'on the fly'. It uses Virtual Device Interface and compresses the stream before writing on disk. This approach allows minimizing disk subsystem load (that is often a bottleneck) and significantly decrease the backup process time.

share|improve this answer

This is a common issue, there are multiple solutions and really depends on your environment. Let's go through them:

1-Backup Compression On The Fly

In 2008 R1 Backup Compression became available in Enterprise, in 2008R2 it became available in Standard. This is HUGE. It will save you A LOT of time. If you can upgrade go for it. If you can't, check out RedGate's HyperBak utility, or Quest LiteSpeed. Both have a free trial.

2- Full and Diff Backups

I inherited a 2TB prod database cause a lot of timeouts for a 24/7 major internet company I worked out. We enabled full and differential backups which saved us a lot of time. I would take a full backup Sunday 12:00am when activity was low, and take diffs during the week. This saved a lot of space. Diff's work different than transaction logs as they work on what database pages were changed. Any changed pages are backed up. Thus you do a full restore, then the diff restore to add the modified pages.

3- What Is Your Bottleneck?

Bottleneck analysis is important to diagnose. Are you backing up to the same disk array as your data files? Are your data files being pegged? What is your DISK SEC/READ and DISK SEC/WRITE for the data disks during backups? I modified the backups to create 4 files. Each file has it's own thread writer and in our SAN that worked out great. Test it out, I shaved off 45 minutes by just creating 4 backup files. Just make sure your disk metrics listed above are low. Get a baseline.

4- Replicate to a Different Server And Back That Up

This one is slightly advanced. You have to make sure your replicated database is up to date and you need proper monitoring for that. If it is though, you can just backup the replicated database.

share|improve this answer
    
Just a heads up on HyperBak, I've had it provide no gains on VLDBs when I tested it in very late 2011. I spoke with RedGate and they mentioned it was a bug, so hopefully it's resolved by now. I don't think 300GB would be considered VLDB anyways but watch out for it if it doesn't perform as expected. –  Ali Razeghi Dec 18 '12 at 19:14

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.