2

I thought the value in msdb.dbo.backupset.compressed_backup_size represented the size, in bytes, of the full, differential, or log backup file (that is, the BAK, DIFF, or TRN file). However, although it is either exact, or very close, for full backup (BAK) files, for log backup (TRN) files, it's so far off that the results seem random.

Steps to reproduce (SQL Server 2008 R2 SP3):

(1). Create a database named 'MyDatabase'. Set it to Full Recovery Model.

(2). Take a compressed full backup of 'MyDatabase'.

(3). Take a log backup of 'MyDatabase'.

(4). Run the following query:

SELECT 
      bs.[database_name]
    , bs.[backup_start_date]
    , bs.[backup_finish_date]
    , bs.[type]
    , bs.[compressed_backup_size]
    , bmf.[physical_device_name] 
FROM 
    [msdb].[dbo].[backupmediafamily] AS bmf
    INNER JOIN [msdb].[dbo].[backupset] AS bs ON bmf.[media_set_id] = bs.[media_set_id]
WHERE 
    bs.[database_name] = N'MyDatabase'
ORDER BY 
    bs.[backup_start_date] DESC

(5). Compare the value in the compressed_backup_size column to the actual size of the files on disk. An example of my results for a log backup are that the 'compressed_backup_size' value displayed is 9505 bytes; the size of the TRN file on disk is 28.0 KB (28,672 bytes).

I've tried this on several servers (with existing databases, as well as with newly created ones) and in all cases the TRN file sizes are reported incorrectly. Is anyone able to reproduce this? Am I misunderstanding the meaning of the compressed_backup_size column?

0

I am seeing similar results (from SQL Server 2008 R2), even though I am not using compression (so compressed_backup_size == backup_size), so I do not think this is a compressed size issue.

For example, for a .bak, backupset reports 117,648,384 while size on disk is 117,678,080, and for a .trn, backupset reports 203,776 while size on disk is 233,472.

I think the documentation's claim that backup sizes are reported as "Total Byte count of the backup stored on disk" is misleading. I suspect that at some level it is reporting the true size of the backup as far its internals are concerned, but the actual size on disk will be larger than that. Although the figures do not seem to divide exactly, I see the disk size as being up to 32KB larger than the reported size.

I just looked at a SQL Server 2012 backup, and it reports 7MB .bak and .trn each being ~10KB smaller than disk size. So this is not just a 2008 R2 issue.

You see the .bak size as "very close" but the .trn size as "so far off" because your .bak is a large size while your .trn is a small size. If it is within ~32KB, the results are not too far off when the .bak/.trn are a decent size, which is what is important.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.