illogical size of databases And How to Backup the Databases

Question

1.- I have executed "reduce databases" on all databases and in the end "Data" occupies 45Gb and "Backup" 5Gb. If I compress "Data and Backup" in a .rar the size is 4Gb. Does this make any logic or has someone screwed up and they NEVER fix it? Any suggestions or just do Zen meditation if your databases are Sql Server?

2.- I also want to ask for a program to make backup copies of all the databases, if it can be reduced before their size. I have been testing a program and I have not liked any.
I am making the copies by clicking on each of the databases and executing "Make backup". at the end I stop each of the Sql Server instances, copy "Data" and "Backup" to another directory and compress that directory. I do it this way because I can't make .rar from the "Data" and "Backup" directories without copying them to another place.

Answer 1

OK, so there is a language barrier here. I understand from your other thread that you are using SQL Server in Spanish, which means that you have to make a guess on what the word is in English. The word is Shrink, not Reduce. (And to make it clear, this is the option which just above Back Up, right)

Comparing shrink to compressing with Rar is like comparing apples to oranges. Shrink is not a compression operation at all. Shrink moves allocated extents of 64 KB to the left side of the file, and unallocated extents to the right side, so to speak. Then it releases the part of the file that only has unallocated extents back to the file system.

If you delete data in table that may lead to extents being deallocated, but it does not always happen. Particularly in heaps (that is, tables without a clustered index) this is common. For maximum effect, it may be a good idea to run ALTER INDEX ALL ON tbl after the purge to minimise the amount of pages with unused space. For a heap, you also need to run ALTER TABLE tbl REBUILD. Once you have done this, you can try the shrink again.

But the size will still be way higher than you achieve with RAR, because, as I said, Shrink is not a compression operation. SQL Server does offer a couple of compression options, ROW, PAGE and two flavours of COLUMNSTORE. However, there is always a tradeoff. The more compressed data is, the more CPU power is needed to access it.

Yet an alternative is to use the compress() function on individual columns, so if you have long columns with a lot of text, you can save space that way. But this also mean that you will need to use the uncompress() function when you access the data.

Answer 2

Hi,

I have executed "reduce databases"

And yet, you think that we can read mind and know what exactly you did?!?

Please provide the exact steps you do including the exact queries if you are looking for serious discussion about technical topic

"Data" occupies 45Gb and "Backup" 5Gb. If I compress "Data and Backup" in a .rar the size is 4Gb. Does this make any logic

Why do you even assume that the default compression in SQL Serer should be smaller than compression to .rar with external tool?!?

When you compress a file to .zip and .rar are these compressions has the same size?

There are multiple compression algorithm and different level of compressions. Each application can use different algorithm and different level of compressions. Even when you compress to the same type of file you can choose these and get different result in the size!

I assume that the SQL Server team choose the compression which best fit for SQL Server performance. It is always a balance between the CPU and memory that is needed for the compression and un-compress and the result size of the compressed file.

Note! Starting with SQL Server 2016 and before SQL Server 2019 (15.x) CU5, If the configuration of the MAXTRANSFERSIZE is not specified, or if MAXTRANSFERSIZE = 65536 (64 KB) or less then the backup compression with TDE encrypted databases may not yield good compression ratios! For more information, see Backup Compression for TDE-enabled Databases.

Note! The default behavior for backup compression is defined by the backup compression default Option server-level configuration option. When you use the command BACKUP DATABASE WITH COMPRESSION then the server compress the backup using the default algorithm and level of compressions. You can change the default compression as explain here: View or Configure the backup compression default Server Configuration Option

Note! Many times external tool can provide better compression then the SQL Server default compression, but remember that when you use external tools to compress the backup result, then you need to uncompress the database backup file before you can start the restore process it self.

personally I use 7z to compress small databases on SQL Server Express which is great solution, but for bigger databases and other editions I will use the build-in compression during backup.

Answer 3

What I am saying is that, if I execute "Reduce databases" and the database occupies 50Gb and the .rar occupies 4Gb it is because the databases are full of spaces, so it has not reduced anything or very little. If the management were optimal, compressing the database with winrar would not gain anything. This situation shows that Sql Server management is highly inefficient and I am wondering if there is any right way, that does not affect performance, to minimize this bad situation.

Answer 4

Hi,

What I am saying is that, if I execute "Reduce databases"

As I said in my first response, I have no idea what you mean but: execute "Reduce databases"

I am not familiar with such command. I recommended to elaborate about the exact steps which you do.

it is because the databases are full of spaces, so it has not reduced anything or very little.

Not clear to me what is the question here. Yes... database file includes space which is not in-used.

Moving from 50 GB to 5 GB is not anything or very little in my opinion (these are your numbers), but this is matter of opinion :-)

This situation shows that Sql Server management is highly inefficient and I am wondering if there is any right way, that does not affect performance, to minimize this bad situation.

If you can find such way then you can offer it to the SQL Server developer team.

It seems like this is the best way that they found.

You can send your offer for better algorithm if you already study this for the last 20 years as they did or if simply you thought about a better way :-)

This is why we have this place: https://feedback.azure.com/d365community/forum/04fe6ee0-3b25-ec11-b6e6-000d3a4f0da0

Remember that the link is to the feedback system, which is not a forum for discussion. You will not get respond there probably and if you want your feedback to be accepted then it is a good idea to get many people to vote for it. With that said, I am sure that if you will come with better algorithm, which balance well between the performance and the compressions size, then you will gain a huge thanks from the team.

You are welcome to post your feedback there.

Here we can only help with technical questions. I think that your original question was answer. No?

If there is anything more which you want to ask, then you are welcome, and if I (or someone else from the community) will know the answer, then we will try to help. Otherwise, you are welcome to mark the answer in order to close the original question.

Answer 5

Spanish:
SEÑORAS Y SEÑORES: UNA COSA LES QUIERO DECIR. QUE TENGO UNA BASE DE DATOS DE 50 GB Y AL MENOS 30GB SON ESPACIOS EN BLANCO Y ESO DESPUÉS DE REDUCIR. AHORA YA ME ENTIENDES Y NO TE LO TENGO QUE REPETIR.

English:
LADIES AND GENTLEMEN: ONE THING I WANT TO SAY YOU. THAT I HAVE A 50GB DATABASE AND AT LEAST 30GB ARE BLANKS AND THAT AFTER REDUCES. NOW YOU UNDERSTAND ME AND I DON'T HAVE TO REPEAT IT.

chinese:
女士們，先生們：我想對你們說的一件事。 我有一個 50GB 的數據庫並且至少 30GB 是空白的，並且在減少之後。 現在你明白我的意思了，我不必再重複了。