July 9, 2019 at 7:29 am
Does anybody have a reliable system for judging how big a compressed native full backup of an OLTP database is likely to be compared to the size on disk? I know that there are a lot of variables that make it hard to say, but is there finger in the air a rule of thumb? (Sorry for the awful mixed metaphor.)
Can I look across an estate and say if there are 10,00 databases with an average size of 500 GB each, I'll need 10,000 * 500 GB * [?] disk space to get one full backup.
Thanks.
July 9, 2019 at 11:09 am
None that I'm aware of. I'd suggest testing on a few disparate data sets, and generate a local one. The compression is consistent, but as you say, very dependent on the data in question.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
July 9, 2019 at 11:35 am
Thanks. I've done some sampling, but I hoped there might be a broad rule I could use estate-wide. Never mind, at least I've had it confirmed.
July 9, 2019 at 11:37 am
Ha! You're placing way more confidence in my answer than I do.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
July 9, 2019 at 12:18 pm
I agree with Grant, it depends on the data in databases and even features/versions you use.
For example, TDE makes compression equal to 0% (zero) on versions before sql 2016.
July 9, 2019 at 1:47 pm
In my experience, the size of the backup is reduced to about 1/6 that of a non-compressed backup.
It does, of course, depend on the contents of your database. If you are storing things in already compressed columns or rows or storing jpegs there is going to be less compression on the backup.
July 9, 2019 at 3:42 pm
If you have 10,000, use sampling. If you know a representative set, use that, otherwise, grab 20-50 dbs, do the backups, and make an educated guess. I assume if you have 10k, they're all similar.
July 9, 2019 at 3:53 pm
If you have 10,000, use sampling. If you know a representative set, use that, otherwise, grab 20-50 dbs, do the backups, and make an educated guess. I assume if you have 10k, they're all similar.
They can even be backed up to NUL device with copy_only and compression.
Check msdb.dbo.backupset.compressed_backup_size and backup_size columns to calculate the ratio you got.
backup database model to disk='NUL' with copy_only,compression,description='test compression',stats=1;
July 17, 2019 at 11:38 am
I hadn't spotted that there were more answers; thanks, all.
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply