Why the size of the backup file changes?

Hi Friends,

Have you ever noticed, while taking a compressed backup the file which gets created initially has a different size than the final file size when the backup completes. To be more precise, the file which gets created initially allocates more space than the final backup file size.

 

But the question is why?

Actually the size of the backup file depends upon the compression settings applied while taking the backup. Now since SQL Server is programmed to get maximum performance, SQL Server calculates the estimated size of the backup and initially allocates the space depending upon an algorithm to avoid frequent growth which results in size changes.

 

Now the question arises does this happens every time?

The answer is No; SQL Server can estimate accurate size of the backup if we are not using any compression settings, based on the size of the Database.

 

Then on what algorithm the SQL Engine decides the backup size?

The size is estimated from the number of allocated extends in the data files, this should be close to the reserved column in the output of sp_spaceused.  In the case of compressed backups, the size depends upon the compression setting which is again dependent upon the data as to how much it can be compressed. In this case SQL server creates the initial file equal to one third of the reserved size of the database. If at any point during backup more space is required the file is extended and if in case the final size is smaller than the initial size (which will be the case most of the time), SQL Server will trim the backup file to the actual used size.

For Example: The DB Size is 50 GB, the target backup size decided by SQL Algorithm comes out to be 17 GB, means the initial size of the backup file would be somewhere around 17GBs but the final size comes to 10GB, because of the compression. The file gets trimmed before releasing the lock since the actual used size was just 10 GBs.

 

Regards

Sarabpreet Anand