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