SQL Server Compressed Backups Disk Space Needs


By:   |   Updated: 2011-11-04   |   Comments (5)   |   Related: More > 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


Last Updated: 2011-11-04


get scripts

next tip button



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.

View all my tips
Related Resources




Post a comment or let the author know this tip helped.

All comments are reviewed, so stay on subject or we may delete your comment. Note: your email address is not published. Required fields are marked with an asterisk (*).

*Name
*Email
Email me updates

Signup for our newsletter

I agree by submitting my data to receive communications, account updates and/or special offers about SQL Server from MSSQLTips and/or its Sponsors. I have read the privacy statement and understand I may unsubscribe at any time.





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

This is really cool.


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

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


Friday, November 04, 2011 - 9:25:14 AM - Yohan Back To Top

OK - THX.


Friday, November 04, 2011 - 8:54:22 AM - Greg Robidoux Back To Top

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 04, 2011 - 7:48:32 AM - Yohan Back To Top

What SQL versions and editions support this feature?

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



download

























get free sql tips

I agree by submitting my data to receive communications, account updates and/or special offers about SQL Server from MSSQLTips and/or its Sponsors. I have read the privacy statement and understand I may unsubscribe at any time.



Learn more about SQL Server tools