INF: Space requirements for backup devices in SQL Server
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.
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.
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.
Article ID: 2001026 - Last Review: 08/11/2011 16:28:00 - Revision: 4.0