MS SQL Server Backup always very big

[bastihan]

Hello everyone,

we use proxmox now for 1 month. (switched from hyper-v) and everything runs very good as we expected.
The only thing i do not understand is that MS SQL Server Backup is daily over 400 GB in size?

Ok the server is quite big


.. but i cant believe that every day there are so much changes?

These are example lines of the backup log:

113: 2023-05-24 23:20:44 INFO: virtio0: dirty-bitmap status: OK (438.9 GiB of 2.5 TiB dirty)
113: 2023-05-24 23:20:44 INFO: using fast incremental mode (dirty-bitmap), 438.9 GiB dirty of 2.5 TiB total
113: 2023-05-24 23:42:41 INFO: transferred 438.85 GiB in 1317 seconds (341.2 MiB/s)

Someone with an idea ?

thanks in advance

 

[tsumaru720]

I'm leaning on experience with other backup solutions here - but whilst there may not be 438GB of changes, there will likely be lots of small changes inside chunk boundaries...

Lets say your chunk sizes are 4MB (I think its 4MB fixed for VMs?) for example and lets say that MSSQL makes a teeny tiny change to a bunch of data all over your disk - PBS will see the hash of the 4MB chunk is different and have to re-upload it - so for a 1kilobyte change could mean a 4MB upload

Multiply that with lots of small changes over the disk and all of a sudden you have a lot to back up.

Example:

MSSQL writes ~110MB of new data - but this is spread all over your disk and happens to work out as 1KB of changes to data within 109,500 chunks - Now you have 109,500 chunks that need to be uploaded, at 4MB each = 438GB of data to send to PBS

This is just my understanding of how it works based on usage of other backup software with similar chunking - happy to be corrected

 

[bastihan]

ok got it .. this would explain the size.
Can anyone confirm this assumption?

If it is true:
Is there a way to reduce the chunk size for example for an MS SQL Server to minimize the backup size ?
Or will this lead to much more time the backup will need to finish?

 

[aaron]

AFAICT the infos are correct. Whatever part of the disk sees a write operation, will be marked as dirty between two backup runs. These are the parts that need to be read. If the chunks are different, the new ones will need to be stored -> probably increased storage usage in the datastore.

Windows in general is quite chatty in my experience. A basic Windows Server install will have a few hundred MiB dirty within no time. In the same time, a basic Debian installation will have a few 10 MiB dirty.

Is there a way to reduce the chunk size for example for an MS SQL Server to minimize the backup size ?

This is not possible at the moment for block based backups. The chunks are set to 4 MiB. The resulting chunk might be smaller if it compresses well.

 

[bastihan]

thanks for the explanation and your time