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

Misleading compression ratio and uncompressed size

shirazshiraz Posts: 9
edited February 13, 2008 9:55AM in SQL Backup Previous Versions
Hi all,

I've noticed that the uncompressed size and the compression % is misleading as they uncompressed size looks as though it is the actual size of the database including free space.

Is this a design feature?

SQL backup is reporting
Uncompressed size = 44.1GB
Compressed size = 9.7GB
Compressed by = 78%

sp_spaceused is reporting
database_size = 45152.94 MB/1024 = 44.1GB
unallocated space = 2380.95 MB/1024 = 2.32GB

reserved data index_size unused



37853048 KB 26670728 KB 10600264 KB 582056 KB

MSDB backupset table is reporting
Backup size = 38759956480/1024/1024/1024 = 36.01GB

From my point of view the Uncompressed size should match that of a Standard MSSQL backup and the Compressed by % should be the ratio between the standard MSSQL Backup and the actual compressed backup size, in the above case it should be 73% not 78%.

Comments

  • Options
    Hi shiraz,
    SQL Backup 4 did use the method that you have described, however for marketing reasons this was changed in SQL Backup 5 to use the "compressed size / database size" ratio, which is used by most other 3rd-party sql server backup utilities.

    I can pass on a feature request if you would like me to, and will see if any compromise could be achieved for a future release.

    Hope that helps,
    Jason
  • Options
    Hi Jason,

    Thanks for the honest responce.

    The compression ratio was accurate when I first reviewed and purchased SQL Backup at version 4.x (I had to do a comparison with litespeed and make a bussineess case). I was quit baffled to see that the uncompressed size of a database that has 50% free space nearly twice the used space. We need to provide accurate information to our clients and are now facing a situation where we are having to justify the figures previously published.

    Anyway my opnion is you should go back to the way it was otherwise to me its lying. Others would say bending the truth.

    Its little things like this that errodes peoples perception in the products they purchase and the companies they deal with, ie how can I be sure of the other stats/figures actually mean something?

    Shiraz
  • Options
    The help file includes the information on how the compression calculation is done.

    http://help.red-gate.com/help/SQLBackup ... tions.html

    It now means that a straight comparison between us and our competitors is easier as we are using the same calculation (e.g. http://www.red-gate.com/products/SQL_Ba ... uation.htm).

    For completeness we could present 2 statistics, the compression percentage compared to the original database size but also the compression percentage compared to the size of the backup which SQL Server would have created. We show the all the information required to make both calculations. The database size and the log size are displayed in the Properties (right click the database in the tree view) and the rest is displayed in the Activity History grid.

    Thanks for your feedback
    Helen Joyce
    SQL Backup Project Manager
    Red Gate Software
  • Options
    peteypetey Posts: 2,358 New member
    If you need to quickly see the compression ratios as calculated using the native backup size as the denominator, you can use the version 4 GUI. If you do not have it, you can download it from http://www.yohz.com/downloads/SQLBackup.zip.
    Peter Yeoh
    SQL Backup Consultant Developer
    Associate, Yohz Software
    Beyond compression - SQL Backup goodies under the hood, updated for version 8
Sign In or Register to comment.