Sessions: 101 The postings on this site are my own and do not represent my Employer's positions, advice or strategies.


  Saturday, July 20, 2024
Wider View Login

IBM - AS400
Performance Counters
Service Broker
SQL Server
     Temporary Database
          Compressed Backups
          Moving the master an...
          Start SQL Server and...
          SQLAgent Job Waiting...
     SQL Mail
     DTS - Data Transformation Services
     Jobs SQL Agent
     User Management
     System Databases
     Temp Tables
Windows OS

Will be added as a sub-category of, Backup
Compressed Backups


While working recently with a  customer, it was brought to my attention that while SQL Server has a great feature to compress backups (introduced in SQL Server 2008), the space consumed by the backup before it is complete may not be as expected.  To be more precise, when you backup a database using compression, the space used by the backup file may actually appear to larger than its “final size”. To be honest, I had not spent much time looking at our compressed backup feature in great detail.

While researching this, I found some people have already discovered this behavior:

Also, as it turns out, we had written a KB article explaining this behavior:

You can see from reading these links that what the engine does when backing up a database using compression, it pre-allocates a file that is a percentage of the estimated final size of the database. Then at the end of the backup if the final size needed is less, we shrink the file to the final needed size. So if you monitor the space used of the backup operation, it is possible to observe that initial file size created is larger then when the backup completes. For example, you may backup a 22Gb database with compression. You may see the file size of the backup show up somewhere past 7Gb while the backup is running but end up only being 4Gb when it completes. As the KB article explains, we chose this method to avoid a performance penalty of having to always grow the file as needed to reach its final size.

The customer I was working with said they didn’t mind a small performance penalty (a possible longer duration for the backup operation)  so they could save on space and only use up the actual size required for the compressed backup.

Thus comes into play trace flag 3042. As you can see from reading the above blog post, trace flag 3042 bypasses the “pre-allocation algorithm” and grows the file as needed. Up until now, this trace flag was officially undocumented and unsupported. But as you can see in the KB article it is now documented. This was a change we just made in the last few days. Behind the scenes, I was able to work with the SQL Product team (thank you Kevin Farlee and the test team for the engine) to have them run the necessary functional tests to ensure the use of the flag would be supported.

I don’t have any numbers on the possible overall performance hit for using this trace flag. The customer I have worked with has said the overall backup time was slightly slower but not impactful and no significant increase in CPU was observed when using this trace flag.

Consider the update to this article as the official support for the use of the trace flag. This flag is supported in SQL Server 2008, SQL Server 2008 R2, and Denali..

Bob Ward