INF: Space requirements for backup devices in SQL Server

Article ID: 2001026 - View products that this article applies to.
Expand all | Collapse all

Symptoms

You perform a compressed backup in SQL Server. When the file of the target backup device is initially created, you may notice that its size is different than the final file size when the backup operation is complete.

Cause

When you perform a backup of a database in SQL Server, the initial and final size of the target backup device can vary depending upon the compression settings applied to the backup operation. To get maximum performance benefits during the backup operation, SQL Server calculates the estimated size of the target backup device and initially creates the backup file at that projected size. This avoids frequent growths that are needed during the backup operation while writing contents to the backup device.

More Information

For uncompressed backups, SQL Server can accurately estimate the size of the target backup device based on the size of the database. This size is calculated directly from the number of allocated extents in the data files that are specified for the backup. The size of the target backup device created will be close to the values shown by the reserved column in the output of sp_spaceused. The target backup device is created with this size during the initial stages of the backup.

For compressed backups, it is not possible to accurately estimate the final size of the target backup device, as it depends on how compressible the data is. SQL Server creates a target backup device with a pre-allocated size that is equal to one third the reserved size of the database that is being backed up. During the backup operation if more space is needed than estimated, the backup file is dynamically extended as needed. At the end of the backup operation, if the final size of the target backup device is smaller than the initial size, the target backup device is trimmed to the size that it actually used for the backup.

As a result, when you create a compressed backup for a database in SQL Server, you may notice, during the initial stages of the backup operation, the space allocated for the target backup device, is different from the final size of the target backup device when the backup operation completes. For example, for a database that was 22 GB in size, the target backup device started out with a size of 7.5 GB, but the final size was just 4.5 GB.

If you enable trace flag 3042, SQL Server does not extend the target backup device to the pre-calculated size during the initial stages of the backup process. When you use this trace flag for a compressed backup, it is possible to see negative performance effects on the backup operation especially when the target backup device is located on a remote network location.

Note This is a "FAST PUBLISH" article created directly from within the Microsoft support organization. The information contained herein is provided as-is in response to emerging issues. As a result of the speed in making it available, the materials may include typographical errors and may be revised at any time without notice. See Terms of Use for other considerations.

Properties

Article ID: 2001026 - Last Review: August 11, 2011 - Revision: 4.0
APPLIES TO
  • Microsoft SQL Server 2008 Developer
  • Microsoft SQL Server 2008 Enterprise
  • Microsoft SQL Server 2008 Enterprise Evaluation
  • Microsoft SQL Server 2008 R2 Developer
  • Microsoft SQL Server 2008 R2 Datacenter
  • Microsoft SQL Server 2008 R2 Enterprise
  • Microsoft SQL Server 2008 R2 Standard
Keywords: 
KB2001026

Give Feedback

 

Contact us for more help

Contact us for more help
Connect with Answer Desk for expert help.
Get more support from smallbusiness.support.microsoft.com