Problem with compression

rlindsay

I am having a problem with getting very little or no compression for a particular database using version 4.6.0.815.

I have run the following with nowrite:
compression set to 0 it is showing data size of 5.9GB, compressed data of 6.4GB and takes 84sec
compression set to 2, compressed data of 5.7GB and takes 677sec

Why would this database not be able to be compressed and why would the compressed size be greater than the data size?

Any help appreciated.

rlindsay

Could this have anything to do with text and image data types?

Brian Donahue

Hello Roger,

SQL Backup typically scores a much lower compression rate with binary data like image data. Text data, historically, has much more efficient compression algorithms available for it, so you typically see 80% compression and higher for text data, but binary data tends to be in a form that doesn't lend itself well to compression.

In one case, I've got an application that first, zipped up data and then stored it into an image column in a SQL database. This database achieved about 2% compression, so I set the compression in SQL Backup to 0 to essentially stop wasting processor power on data that was already compressed, which also sped up the backup performance quite a lot.

rlindsay

Brian,

Thanks for that, it may be worthwhile adding that information to the troubleshooting section in the help file.

Thanks
Roger