Condividi tramite

How compressed is your backup?…

  • Articolo

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:

https://adventuresinsql.com/2010/02/why-do-my-sql-2008-compressedbackups-shrink/

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

https://support.microsoft.com/kb/2001026

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
Microsoft

Comments

  • Anonymous
    August 11, 2011
    Thanks Bob... This post comes right in time when we are re-architecting our backups logic of entire SQL Server landscape. Just one questions: The KB mentions "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"...is this 1/3rd size calculation fixed or can vary. Info will help us to plan the space provisioning.

  • Anonymous
    August 12, 2011
    Flag 3042 seems to be very cool. It is included from SQL Server 2008 RTM, isn't it? It would be way more cooler if customers of SQL Server 2008 Standard could use backup compression at all. Any plans of enabling this feature for your customers? Thank you.

  • Anonymous
    August 15, 2011
    Good that Microsoft now officially support this trace flag.

  • Anonymous
    December 27, 2011
    We tested 2 scenarios for the SQL Server 2008 Enterprise Edition Compressed backups:

  1. Took a SQL Server 2008 Compressed Full backup of 600 GB “TEST_DATABASE” Database on the G: drive with enough free space of more than 300 GB • SQL Server created a pre allocated 111 GB for the Full backup • Took 1 hr 45 mins • The final backup was 133 GB in size which is more than the pre allocated backup file
  2. In the second scenario we kept only 100 GB free space on the G: drive to test the behavior of the Pre allocated backup file by SQL Server • SQL Server this time did not create any backup file with a pre allocated size • The backup this time behaved like a normal SQL Server backup file which grew every few minutes Conclusion: SQL Server 2008 Compressed backup tends to behave like a normal backup when it does not have enough space on the disk to pre-allocate the backup file
  • Anonymous
    September 18, 2012
    Bob, what about when doing backups using SQL VDI, would this behavior be the same?