INF: SQL Server Compressed backup may work fine if disk space is less and may fail if disk space is more

Article
03/20/2012

In this article We are going to discuss about pre-allocation scenarios and their aftermaths for SQL Server 2008 compressed backup. We will try to simulate that compressed backup may succeed if disk space is less and may fail if disk space is more depending on the pre-allocation file size and final compressed backup size.

Simulation:

We have created a database of 84 GB for which full backup size is 82.9 GB. We also enabled the backup compression default at the instance level using:
sp_configure 'backup compression default',1
reconfigure with override

Let us execute following command to take backup:

 BACKUP DATABASE [test_bkup_comp] 
TO DISK = 'T:\backup\tbc_1_3_run1.dmp', 
   DISK = 'T:\backup\tbc_2_3_run1.dmp', 
   DISK = 'T:\backup\tbc_3_3_run1.dmp' 
WITH INIT, NAME='tbc1', DESCRIPTION =  'Striped compressed backup'

As soon as backup initiates, it pre-allocates three files of approximately 9.2 GB as shown below:

After backup completion, it generates 3 files of approximately 2.05 GB as shown below.

This is correct behavior and already documented in the KB article https://support.microsoft.com/kb/2001026 .

Over here we had enough disk space to accommodate all the pre-allocated files.

Now, let us simulate a situation where we have disk space just enough to pre-allocate one file. Let us say 10.7 GB as shown below:

I executed following backup command:

 BACKUP DATABASE [test_bkup_comp] TO 
    DISK = 'T:\backup\tbc_1_3_run2.dmp', 
    DISK = 'T:\backup\tbc_2_3_run2.dmp', 
    DISK = 'T:\backup\tbc_3_3_run2.dmp' 
WITH INIT, NAME='tbc2', DESCRIPTION =  'Striped compressed backup'

In this situation, the space is not enough to create three pre-allocation files. So what SQL Server does, is create maximum number of pre-allocation files and for remaining backup stripes create 0 KB files to start with and increases its size as the backup proceeds as shown below:

The available disk space keeps on decreasing and the 0KB file (created initially) keeps on increasing as backup proceeds as shown below:

Finally when the available space becomes zero, the backup fails with following error:

Msg 3202, Level 16, State 1, Line 1

Write on "T:\backup\tbc_3_3_run2.dmp" failed: 112(There is not enough space on the disk.)

Msg 3013, Level 16, State 1, Line 1

BACKUP DATABASE is terminating abnormally.

Now, we decreased the disk space to 8.6 GB, with an assumption that backup will fail again due to lack of disk space:

Then we executed the backup command:

 BACKUP DATABASE [test_bkup_comp] TO 
    DISK = 'T:\backup\tbc_1_3_run3.dmp', 
    DISK = 'T:\backup\tbc_2_3_run3.dmp', 
    DISK = 'T:\backup\tbc_3_3_run3.dmp' 
WITH INIT, NAME='tbc3', DESCRIPTION =  'Striped compressed backup'

We found that it didn’t go for any pre-allocation and generated three 0KB files as shown below:

Now as the backup proceeds, these 0KB files increase in size until backup got over. As shown below, the final compressed backup files stripes (of size 2.05 GB approx.) were created after successful backup:

Here it works as if we have enabled trace flag 3042.

So, this clearly states that backup is not failing during pre-allocation. Backup tries to create maximum number of pre-allocated files depending on disk space available and for rest of stripes, it creates 0KB files, which grow in size as the backup command proceeds. Only hindrance in this process is disk space. If we have less disk space for the compressed size of stripes of backup, the backup command will fail.

To simulate this we had to create a scenario.

If our logic is correct, let estimate for the disk space required for this simulation

Size of 1 pre-allocated stripe = 9.6 GB

Size of 1 Stripe compressed backup = 2.1 GB

This means, total size required = 9.6GB + 2.1 GB + 2.1 GB = 13.8 GB

Let us take overhead of 10% = 1.38 GB

Rounded it to 1.4 GB

Now the space requirement is 13.8+1.4 = 15.2 GB. So, 15.2 GB is not enough for 3 stripes of backup however it is sufficient enough to accommodate 1 pre-allocated stripe and two compressed backup stripes.

If the backup goes successful, we should have 15.2 – 2.1 – 2.1 – 2.1 = 9.9 which is nearly equal to 10 GB.

Over here we could free up space of 15.6 GB so, after successful backup, space available with disk should be near to 9.4 GB.

I executed following command:

 BACKUP DATABASE [test_bkup_comp] TO 
    DISK = 'T:\backup\tbc_1_3_run4.dmp', 
    DISK = 'T:\backup\tbc_2_3_run4.dmp', 
    DISK = 'T:\backup\tbc_3_3_run4.dmp' 
WITH INIT, NAME='tbc4', DESCRIPTION =  'Striped compressed backup'

As expected backup created 1 pre-allocated file of size 9.2 GB and two stripes of 0KB files. The 0KB files started to increase in size once the backup proceeded.

As predicted, the backup completed successfully and we get following confirmation from SQL Server:

Processed 10871048 pages for database ' test_bkup_comp’, file ' test_bkup_comp' on file 1.

Processed 3 pages for database ‘test_bkup_comp’, file ' test_bkup_comp_log' on file 1.

BACKUP DATABASE successfully processed 10871051 pages in 1729.885 seconds (49.095 MB/sec).

At this point of time we could see that the file size of each stripe was nearly 2.05 GB.

And the disk space available was around 9.46 GB which is close to our calculation.

Summary

This is just a rough calculation to make my life bit easier for a 84 GB database compressed backup into three stripes as shown in the above example

Tabular Representation of scenario presented in the article

S. No

Disk Space Available (in decreasing order)

Backup Result

Remarks

Greater than 28 GB

Success

Space available to pre-allocate all the three stripe files of size 9GB each.

Between 22GB and 28 GB

Success

Space available to pre-allocated files two 9GB files each + 1 compressed backup file of size 2GB

Between 18GB and 21 GB

Fails

Space not available to pre-allocated two 9GB files each + 1 compressed backup file of size 2GB

Between 14GB and 17 GB

Success

Space available to pre-allocated files one 9GB file + 2 compressed backup files of size 2GB each

Between 9GB and 13 GB

Fails

Space not available to pre-allocated one 9GB file + 2 compressed backup files of size 2GB each

Between 7GB and 8 GB

Success

Space available generate 3 compressed backup files of size 2GB each

6GB or less

Fails

Space not available generate 3 compressed backup files of size 2GB each

There is a possibility that compressed backup succeeds if there is less space available and fails if there is more space available on the disk where compressed backup is taken.

Conclusion:

Compressed backup generates maximum possible pre-allocated files in the disk where backup is being taken.
If the space is less to create stripes of pre-allocated backup, it generates 0KB files for remaining stripes.
The 0KB files increase in size as the backup proceeds
The backup succeeds if it has enough space to accommodate the compressed backup file (from point 3) and pre-allocated files (from point 1), otherwise the backup fails.
Our pre-assumption was wrong that backup fails since it was not able to create one or more pre-allocated files.
This explains the scenario when the backup fails to create none of the pre-allocated files. In this scenario, it will create 0KB files for all stripes and it will increase in size once backup proceeds. Backup will succeed if there is enough disk space available to accommodate all the compressed backup stripes.
Pre-allocation “effect” of shrink of the compressed backup file is clearly explained in the article (written by Bob Ward) https://blogs.msdn.com/b/psssql/archive/2011/08/11/how-compressed-is-your-backup.aspx however it doesn’t throws light on our scenario.

Written By:

Shivendra Vishal - Support Engineer,Microsoft GTSC

Reviewed By:

Pradipta Das - Technical Lead, Microsoft GTSC

Share via

INF: SQL Server Compressed backup may work fine if disk space is less and may fail if disk space is more

Additional resources