While we were using the SQL Server 2008 R2 native backup compression to replace the Redgate SQL Backup, we noticed a huge backup file size increase at the beginning of the backup which basically nearlly full up of our backup disk volume.
The database we were backing up close to 2 Terabyte and by using the SQL backup with compression, the final backup size was roughly 376GB as below:
When we used the SQL server native backup with compression
Backup database MyDB to disk = N'I:\Backups\CompressionBackup_Without_TF.bak' With init, compression
the initial file size has gone up to 638GB, which is roughly one third of the original database size, as below:
This is a huge 262GB backup file size increase, which was totally thrown away our Backup disk capacity plan based on the Redgate compression ratio.
Surprisingly, once the backup completed the file size shrank back to around 380GB which is similar size as the SQL backup compressed file.
This is indeed a default behaviour of the SQL Server Native backup compression. for a compressed backup, it is hard for SQL server to determine what is the final size of the backup. simply, it creates a initial backup files size with 1/3 of the database size (pre-allocation algorithm). In case during the course of the backup more disk space is needed, it will dynamycally extend the file as needed. In case the final backup size is smaller thant the initail 1/3 of the database size, it will truncate the file to the actual.
This is not good enough for us as I mentioned above that we have already planned the capacity based on the Redgate SQL Backup. In some point that we may not have enough disk space to hold the initial backup files. We really want to have the actual size required for the compressed backup.
This issue was solved by introduce a trace flag 3042. The trace flag 3042 bypasses the “pre-allocation algorithm” and grows the file as needed with small performance penalty.
This trace flag was not offically documented but a few month ago it was announced in micorosoft support see the articale Space requirements for backup devices in SQL Server
By using the trace flag, you can notice that the initil backup file size is 0 and will grow up to the final size.
Backup compression with trace falg 3042 on:
dbcc traceon(3042)
Go
Backup database BiW to disk = N'I:\Backups\CompressionBackup_With_TF.bak' With init, compression
Go
dbcc traceoff(3042)
Go
The initial file size was 0 as below:
You can turn on this trace flag globally by adding the -T3042 to the startup parameters.