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

Speed of Compression 4 on Large Database 200Gb plus

ChrisAVWoodChrisAVWood Posts: 361
edited September 28, 2009 12:04PM in SQL Backup Previous Versions
I am testing the tradeoffs of the compression levels for a 200Gb plus database using

We usually use level 2 and this compressed the backup slightly more than 5.4 level 2. Both took just about the same length of time to run. We also run with verify that obviously adds to the time taken for the full job. Running level 3 took a further 2Gb of the backup size of 20Gb but takes 20 plus minutes longer for both 5.4 and 6.2. When I tried level 4 it about 3 times as long to run but the compression is about 40% better.

So to sum up level 2 takes a little over 40 minutes plus close to 30 minutes to verify and makes a 20Gb backup. No real difference between 5.4 and 6.2.

A level 3 takes over an hour plus about 28 minutes to verify for a 18.2 Gb backup. Again no real difference between 5.4 and 6.2.

A level 4 takes about 3 hours 30 minutes plus close to an hour to verify and produces an 11.4Gb backup.

Looks like level 4 will certainly compress much more but at a very high execution time penalty.

I do like the log output and the diskretries works as expected even though it could be painfull if you don't manage the disk space properly.

English DBA living in CANADA


  • Options
    Hi Chris,

    By my recollection, the compression algorithms haven't changed -- it's just the addition of level 4 that was the change between versions 5 and 6. Level 4 will be the most CPU-intensive.
  • Options

    I am just relating that from my limited experience with 6.2 that the level 4 does a wonderfull job of super comression but it comes with a high elapsed time penalty. My other results show that 6.2 has not increased the elapsed times of level 2 and level 3 backups as you expressed.


    English DBA living in CANADA
Sign In or Register to comment.