What are the challenges you face when working across database platforms? Take the survey

Problem with compression

rlindsayrlindsay Posts: 17
edited October 26, 2006 3:37AM in SQL Backup Previous Versions
I am having a problem with getting very little or no compression for a particular database using version

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.


  • Options
    Could this have anything to do with text and image data types?
  • Options
    Brian DonahueBrian Donahue Posts: 6,590 Bronze 1
    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.
  • Options

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

Sign In or Register to comment.