question

Wrencher avatar image
Wrencher asked

SQL Backup Compression

Good evening, We recently switched our ERP system from the native database to SQL. SQL Server 2008 r2 Standard. With support from from ERP system folks, we have maintenace set up to do a nightly full backup and hourly transaction logs during work hours. We have a separate spindle for backups on the server. We originally didn't compress and could only get about 2 backups and 2 sets of logs on the drive. After doing some research and confirming with our support folks, I have the full backup job set to compress. However, they mentioned not to turn compression on for the hourly logs as it may slow things down. The logs are pretty big and I'm using 7zip to compress them so they can be copied to another location on the network for backup. What are the thoughts on the server preformance if compressing the tlogs is turned on?
backup-compression
3 comments
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

How big is "pretty big"?
4 Likes 4 ·
@ThomasRushton - "Pretty big" is between "Not really what I'd call small" and "OMG thats huge"
3 Likes 3 ·
When I first glanced this I thought you were asking how big a "pretty pig" was...and I thought it probably "depends" on what lip-stick is on a pig...ahhh, first signs of dyslexia folks..take note
0 Likes 0 ·
KenJ avatar image
KenJ answered
It will probably have a smaller impact than using 7 zip to compress them. Run some off hours testing and measure the processor difference between compressed and non. I think you'll find you'll be just fine with compression turned on. If the worst happens and compression pushes things over the top, you can always fall back to non- compressed
2 comments
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

I'm with @KenJ on this. Backup compression costs you CPU, unless you are seeing constant high CPU usage on your server (over 60% - a bad sign in itself) then you are unlikely to even notice the performance hit. Try it out, then turn it off if it hurts. However, you are 7zipping the files on the same server, so using the CPU horsepower regardless, so don't see you being better off with 7zip.
3 Likes 3 ·
and the overhead on the CPU of compression is likely to be less than the overhead of writing out much larger TLogs... particularly when you look at moving those larger files around the disks / networks etc.
1 Like 1 ·
Pavel Pawlowski avatar image
Pavel Pawlowski answered
If the T-Log backups are "pretty big" then I suggest you shorten the intervals between T-Log backups. Then you will receive more, but much smaller backups which will be better manageable an easier to transfer over network etc.
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Wrencher7827 avatar image
Wrencher7827 answered
Okay - I think I've found the right forum for my SQL help needs. Good info with a dose of humor! Thanks everyone. Yes, pretty big can be relative, our total for a day for 9 tlog backups(no laughing now) +/- 1GB. Our DB backup went from 37GB to 6GB once I turned compression on. The reason behind this interest in compressing is to get three days info on the server backup drive, then after the last .bak file of the day, schedule a copy that will copy just the "current day's" files to the backup server. If the tlogs compress as much as the .bak file did, I should end up with appx 7gb to copy across our internal network during off hours. Thanks again!
4 comments
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

why do you ask the question with one account but answer with another? You will not be able to mark the question as answered unless you use the original account. Just wondering.
1 Like 1 ·
@meltondba, I think he had post the question without registering. I know it was possible in the past and probably also now. Then again answered with new one.
0 Likes 0 ·
Editing powers to the rescue. Assuming I picked right.
0 Likes 0 ·
Good evening, Pavel, you are correct as to the reason for "two" IDs. From now on, I'll use the 7827 id to avoid confusion. Wrencher7827
0 Likes 0 ·

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.