Backup size just before compression

When backup is on processes the size is more than three times after it completed. The maximum database compression seems takes place at the end of the backup processes. This is a problem for backing big size databases. For instancein my experience for 500GB database, the backup size during taking backup is 171GB and that size drop to 55GB when the backup completed. Any advice how to keep the backup at minimum size during taking the backup?

more ▼

asked Nov 14, 2017 at 03:16 PM in Default

avatar image


(comments are locked)
10|1200 characters needed characters left

1 answer: sort voted first

Yes. See https://docs.microsoft.com/en-us/sql/relational-databases/backup-restore/backup-compression-sql-server and use trace flag 3042.

TL;DR: it's not that the backup is taken and then compressed, but the backup file is pre-allocated at that size (roughly 1/3rd the size of the DB) and then grown when necessary during the backup and trimmed back at the end of the backup. The compression is done to the data while it is being written out to the backup file, not afterwards.

more ▼

answered Nov 14, 2017 at 04:32 PM

avatar image

ThomasRushton ♦♦
42.2k 20 57 53

(comments are locked)
10|1200 characters needed characters left
Your answer
toggle preview:

Up to 2 attachments (including images) can be used with a maximum of 524.3 kB each and 1.0 MB total.

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here



Answers and Comments

SQL Server Central

Need long-form SQL discussion? SQLserverCentral.com is the place.



asked: Nov 14, 2017 at 03:16 PM

Seen: 24 times

Last Updated: Nov 14, 2017 at 04:35 PM

Copyright 2018 Redgate Software. Privacy Policy