By: Matteo Lorini | Comments (5) | Related: > Compression
Problem
SQL Server 2008 and later offers the ability to create compressed backup files. When creating the compressed backup, how much space is really needed and when does the space get allocated for the backup file? In this tip we take a look at how compressed backup files are created with an initial file size and also a way to incrementally build the backup file with a trace flag.
Solution
More than once I have heard that when you create a SQL 2008/R2 native backup with compression, SQL will backup the entire database on disk and when the backup is complete, SQL compresses the file. I did some research and found this interesting article "Space requirements for backup devices in SQL Server" which says: "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".
So based on the above, I want to show and verify that compression occurs in memory and not at the disk level.
Initial Backup
My starting point is my BACKUP_TEST database that is around 2.7GB.
When I start the backup (compression is on as the default), I can see that the initial size on disk is set to ~731MB. This is about 1/3 of the database size which the KB article states would be the allocated size.
When the backup completes, it shows the backup file is ~644MB, so this is a little less than the estimate, but the file never got any bigger for this backup. When the backup completes, the final size is less than the previously estimated size which means that SQL was able to use less than the previously estimated disk space and therefore released the over allocated disk space back to the OS.
Using Trace Flag 3042
If we enable trace flag 3042, we can have the file incrementally build instead of SQL Server pre-allocating the file size. We will create another backup file, so we can compare the results.
DBCC TRACEON (3042, -1);
GO
BACKUP database BACKUP_TEST to disk = '\\TestServer\TestBackup\BACKUP_TEST3042.BAK'
As you can see below, from the multiple screen shots, SQL incrementally allocates disk space during the backup operation when using this trace flag and the files grows as needed.
And finally, we can see our completed backup is the same size as our first backup.
Conclusion
As shown in the above examples, the final backup file size is smaller than the original database size and SQL never pre-allocated a file backup size as big as the original database size. Also, the size of the target backup is close to 1/3 of the size shown by the reserved column in the output of sp_spaceused stored procedure.
Most SQL Server databases are highly compressible, so using compressed backups is a great way to save disk space and also speed up backup times. In addition to SQL Server native compression there are also several third party compressed backup tools which you should research as well.
Next Steps
- Review these related tips:
About the author
Matteo Lorini is a DBA and has been working in IT since 1993. He specializes in SQL Server and also has knowledge of MySQL.This author pledges the content of this article is based on professional experience and not AI generated.
View all my tips
