Sql-server – SQL Server database backup file is double final size until backup completes

backupsql-server-2008

I've just completed writing a backup statement for a reasonably large database (1.3 TB), and I've been testing it out using a much smaller database (10 GB + 10GB log file). The backup writes to three destinations. During the testing I noticed that whilst the backup is running each backup file is about 1.2 GB in size, but once the backup process has completed the files shrink to less than 600 MB.

I'm interested in knowing:

  • why the files are bigger during the backup process and then shrink?
  • whether there is a way to prevent this,
  • how big the files are likely to be when I back up that 1.3 TB database – if the files are again going to be double their final size then I may have to ask for some more disk space!

Here's the backup statement that I'm using:

BACKUP DATABASE @DatabaseName
TO  DISK = @backupMedia1,
    DISK = @backupMedia2,
    DISK = @backupMedia3
WITH COMPRESSION, RETAINDAYS = 0, NOFORMAT, INIT, NOSKIP, NAME = @DatabaseName

Best Answer

This is a known issue and expected behavior (it is by design). Essentially, the backup reserves the total amount of space that it thinks it might need, then shrinks as the last step. To change the behavior you can experiment with trace flag 3042. From http://msdn.microsoft.com/en-us/library/bb964719.aspx#Allocation :

For compressed backups, the size of the final backup file depends on how compressible the data is, and this is unknown before the backup operation finishes. Therefore, by default, when backing up a database using compression, the Database Engine uses a pre-allocation algorithm for the backup file. This algorithm pre-allocates a predefined percentage of the size of the database for the backup file. If more space is needed during the backup operation, the Database Engine grows the file. If the final size is less than the allocated space, at the end of the backup operation, the Database Engine shrinks the file to the actual final size of the backup.

To allow the backup file to grow only as needed to reach its final size, use trace flag 3042. Trace flag 3042 causes the backup operation to bypass the default backup compression pre-allocation algorithm. This trace flag is useful if you need to save on space by allocating only the actual size required for the compressed backup. However, using this trace flag might cause a slight performance penalty (a possible increase in the duration of the backup operation).

Related Question