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?
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
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.
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!