November 7, 2016 at 6:23 am
We've been having on and off space issues with a backup for a vended database. This backup is part of the month end process and for some reason, it failed when it was about 200 GB. Our daily compressed backups end up about 40 GB.
The boss is concerned about the vendor's code given the difference in sizes. But my response (and here's where I need the verification) was that the backup process doesn't actually compress at the start of the backup. That the process grabs a "footprint" of space for the backup uncompressed and if it fails, that's why the size is so large, because it didn't have time to hit the compression part.
My working theory is the backup starts as a regular backup first, then compresses as it gets to the end of the backup. I base this off the following line from MSDN:
For compressed backups, the size of the final backup file depends on how compressible the data is, and this is unknown before the backup operation finishes.
Am I reading this wrong (and making a wrong theory assumption) or am I reading this correctly?
November 7, 2016 at 6:29 am
No, a compressed backup is compressed from the start.
If it worked according to your theory, then a compressed backup would take longer than a normal backup (write out the uncompressed backup, compress it, write again). That's not the case.
The line from MSDN refers to the file size. When SQL allocates a file for the backup (empty file, instant initialised if the SQL service account has the correct permission), it allocates it at a size larger than it probably needs, because it doesn't want to have to reallocate space during the backup. The compressed backup is written into the file and, at the end, the file size is adjusted to remove unused space at the end of the file.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
November 7, 2016 at 6:47 am
See also https://support.microsoft.com/en-us/kb/2001026 - a MS Fast-publish article about backup space.
See also TF 3042 - https://msdn.microsoft.com/en-us/library/ms188396.aspx which doesn't do the pre-allocation of space when creating a compressed backup. Might help if your free disk space is marginal.
Thomas Rushton
blog: https://thelonedba.wordpress.com
November 7, 2016 at 6:48 am
GilaMonster (11/7/2016)
No, a compressed backup is compressed from the start.If it worked according to your theory, then a compressed backup would take longer than a normal backup (write out the uncompressed backup, compress it, write again). That's not the case.
The line from MSDN refers to the file size. When SQL allocates a file for the backup (empty file, instant initialised if the SQL service account has the correct permission), it allocates it at a size larger than it probably needs, because it doesn't want to have to reallocate space during the backup. The compressed backup is written into the file and, at the end, the file size is adjusted to remove unused space at the end of the file.
Then why do our compressed backups fail with an overly inflated size when there's a space issue?
We have enough space for a couple of 40 GB backups, but the backup file (in process) ended up being 200 GB.
EDIT: Nevermind. Thomas's first article link answers my question. Thanks.
November 7, 2016 at 6:54 am
Brandie Tarvin (11/7/2016)
GilaMonster (11/7/2016)
No, a compressed backup is compressed from the start.If it worked according to your theory, then a compressed backup would take longer than a normal backup (write out the uncompressed backup, compress it, write again). That's not the case.
The line from MSDN refers to the file size. When SQL allocates a file for the backup (empty file, instant initialised if the SQL service account has the correct permission), it allocates it at a size larger than it probably needs, because it doesn't want to have to reallocate space during the backup. The compressed backup is written into the file and, at the end, the file size is adjusted to remove unused space at the end of the file.
Then why do our compressed backups fail with an overly inflated size when there's a space issue?
Um, I answered that.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
November 7, 2016 at 6:59 am
GilaMonster (11/7/2016)
Brandie Tarvin (11/7/2016)
GilaMonster (11/7/2016)
No, a compressed backup is compressed from the start.If it worked according to your theory, then a compressed backup would take longer than a normal backup (write out the uncompressed backup, compress it, write again). That's not the case.
The line from MSDN refers to the file size. When SQL allocates a file for the backup (empty file, instant initialised if the SQL service account has the correct permission), it allocates it at a size larger than it probably needs, because it doesn't want to have to reallocate space during the backup. The compressed backup is written into the file and, at the end, the file size is adjusted to remove unused space at the end of the file.
Then why do our compressed backups fail with an overly inflated size when there's a space issue?
Um, I answered that.
Sorry about that. My only excuse is post-surgery drugs that are causing eye-focusing issues. I can read, except I can't quite.
November 7, 2016 at 7:03 am
Brandie Tarvin (11/7/2016)
GilaMonster (11/7/2016)
No, a compressed backup is compressed from the start.If it worked according to your theory, then a compressed backup would take longer than a normal backup (write out the uncompressed backup, compress it, write again). That's not the case.
The line from MSDN refers to the file size. When SQL allocates a file for the backup (empty file, instant initialised if the SQL service account has the correct permission), it allocates it at a size larger than it probably needs, because it doesn't want to have to reallocate space during the backup. The compressed backup is written into the file and, at the end, the file size is adjusted to remove unused space at the end of the file.
Then why do our compressed backups fail with an overly inflated size when there's a space issue?
We have enough space for a couple of 40 GB backups, but the backup file (in process) ended up being 200 GB.
EDIT: Nevermind. Thomas's first article link answers my question. Thanks.
My thought is that it is using a pessimistic / conservative compression ratio estimation when allocating the initial space for the compressed backups.
😎
November 7, 2016 at 7:46 am
ThomasRushton (11/7/2016)
See also https://support.microsoft.com/en-us/kb/2001026 - a MS Fast-publish article about backup space.See also TF 3042 - https://msdn.microsoft.com/en-us/library/ms188396.aspx which doesn't do the pre-allocation of space when creating a compressed backup. Might help if your free disk space is marginal.
I did some work with that trace flag. (if anyone is interested in my post)
https://blobeater.wordpress.com/category/sql-server-2/"> https://blobeater.wordpress.com/category/sql-server-2/
November 7, 2016 at 7:51 am
BLOB EATER (11/7/2016)
ThomasRushton (11/7/2016)
See also https://support.microsoft.com/en-us/kb/2001026 - a MS Fast-publish article about backup space.See also TF 3042 - https://msdn.microsoft.com/en-us/library/ms188396.aspx which doesn't do the pre-allocation of space when creating a compressed backup. Might help if your free disk space is marginal.
I did some work with that trace flag. (if anyone is interested in my post)
https://blobeater.wordpress.com/category/sql-server-2/"> https://blobeater.wordpress.com/category/sql-server-2/
That depends. Do we get popcorn to eat while reading it? @=)
Thanks for the link. I'll check it out.
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply