January 17, 2013 at 5:44 pm
Hi All,
I have a server with a few DB's on SQL Server 2008R2 and, as one of the main OLTP DB's is growing, I decided to try backup compression for my local backups. Basically, I'm trying to implement a full weekly backup and daily differentials (my SLA allows for up to a day's data loss). My backup drive is 100GB and the total size of the DB's I'm trying to back up is roughly 80GB, with the largest of them currently at 75GB. I was hoping that by implementing backup compression I'd be able to avoid having to increase my backup drive, but a couple of things:
* When I try to back up with compression, it fails saying it ran out of space while writing the large DB even though after failure, the drive still shows 30+GB available. Why is this? Why would the compression process require more space (assuming that that's what causes the out-of-space failure)?
* The large DB, reached 63GB before it failed, which is not a compression rate I was expecting. I know the bulk of the data here is BLOB's, could that be the reason for the unexpectedly poor compression rate? The other DB's do seem to get great compression rates.
* If I back up the same DB's without compression, the process succeeds. Of course, the issue is that as the DB's continue to grow, I'll find myself in the situation I'm trying to avoid.
Any help on this will be appreciated.
TIA,
-- Igor
January 18, 2013 at 7:13 am
BLOBs do not compress well, if at all. Sometimes the compressed version of a BLOB can be bigger than the decrypted BLOB due to the encryption overhead combined with an effective rate of 0% data compression.
Encrypted data also does not compress well.
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
January 18, 2013 at 10:01 am
MyDoggieJessie (1/17/2013)
I believe your questions are answered here
Thanks!
I looked at that, but it seems like the pre-allocation of space starts out at one third of the DB's allocated space. I actually see that happening. After that's filled up, I also see the file growing until it reaches the ~63GB mark, then it fails. My point is that, based on my observation, there are still 30+GB available on the drive when this occurs.
To test further, I removed one DB from the backup process (final compressed backup for this DB was ~6GB), which meant that there were ~6GB more space available on the backup drive. I ran the backup and it succeeded this time. The final compressed size for the large DB backup was ~63.5GB.
:crazy: Head scratcher for me
January 18, 2013 at 10:02 am
opc.three (1/18/2013)
BLOBs do not compress well, if at all. Sometimes the compressed version of a BLOB can be bigger than the decrypted BLOB due to the encryption overhead combined with an effective rate of 0% data compression.Encrypted data also does not compress well.
Thank you. That was my suspicion.
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply