SQL Server Compressed Backups Disk Space Needs

By:   |   Updated: 2011-11-04   |   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.

sp_spaceused

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.

sql 2008 backup compression

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.

sql backup


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.

sql backup compression
sql 2008 r2 backup compression
backup compression sql
sql server 2008 backup compression
backup compression sql server
sql server 2008 r2 backup compression

And finally, we can see our completed backup is the same size as our first backup.

sql server backup compression


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


sql server categories

sql server webinars

subscribe to mssqltips

sql server tutorials

sql server white papers

next tip



About the author
MSSQLTips author Matteo Lorini 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


Article Last Updated: 2011-11-04

Comments For This Article




Wednesday, July 16, 2014 - 5:34:31 PM - Kish Back To Top (32742)

This is really cool.


Friday, November 11, 2011 - 3:36:06 PM - Naren Back To Top (15081)

Hi Thanks for your artile . It is really impressive to know about this info and will be useful to the project


Friday, November 4, 2011 - 9:25:14 AM - Yohan Back To Top (15021)

OK - THX.


Friday, November 4, 2011 - 8:54:22 AM - Greg Robidoux Back To Top (15019)

SQL Server 2008 Enterprise edition and SQL Server 2008 R2 Standard, Enterprise and Datacenter editions.  This will also work in Developer and Evaluation versions as well.

See this as well: http://msdn.microsoft.com/en-us/library/bb964719.aspx


Friday, November 4, 2011 - 7:48:32 AM - Yohan Back To Top (15017)

What SQL versions and editions support this feature?

I have some SQL 2008 boxes and some SQL 2005 boxes.  All are STD edition.















get free sql tips
agree to terms