I've got a new SQL Server 2014 (12.0.2000) instance with about 50 databases, it was recently upgraded from SQL Server 2005.
They were using a lot of backup space so the old maintenance plan was replaced to a different approach:
Check Db Integrity -> Back up Database (Full) (with Compression) -> Rebuild Index -> Maintenance Cleanup
That happens everyday while databases aren't being used and it preserves backups for 5 days.
At the beginning it worked OK and a lot of backup drive was saved but then some of the databases started backing up with erratic file sizes.
A few images that display the file size change:
This one went down a whole lot, that was expected. Thing is, it then when down another two times (?)
This other one when down a bit and then further down in size and finally got bigger than the first time it backed up with compression.
Finally this one went down in size, further down next day and then up the that place it was the first day.
In this case:
On restoring, the file the size increases quite a bit:
So they do restore the way they're supposed to but I'm here wondering, how come they vary in size, I can totally understand the change after the compression but after (?).
Why would the backup behave in this way? Any known reasons?
Additionally: Nothing really going on in the logs.