Unstable backup size, where to look for causes

Question

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.

Answer 1

When using backup compression, the size of the backup will vary quite a lot assuming data is being changed in the database.

Performing index maintenance each night will move data around inside the database a lot, resulting in unpredictable changes to the amount of allocated pages inside the database. Index defragmentation is not perfect and does not guarantee the index is completely defragmented - it is merely a way of reducing fragmentation.

The amount of change you're seeing is nothing to be concerned about, especially when you see your file sizes are measured in kilobytes.

Answer 2

Well, if this was me, I think I'd first rule out REBUILD consolidating data into significantly fewer active pages (since BACKUP only backs up active pages)

I think I'd restore a 'large' backup to a test server and run the following query to identify used_pages per object. Save that information off for comparison to the restore of a 'smaller' backup

SELECT  OBJECT_SCHEMA_NAME(s.object_id) schema_name,
        OBJECT_NAME(s.object_id) table_name,
        SUM(s.used_page_count) used_pages,
        SUM(s.reserved_page_count) reserved_pages
FROM    sys.dm_db_partition_stats s
JOIN    sys.tables t
        ON s.object_id = t.object_id
GROUP BY s.object_id
ORDER BY schema_name,
        table_name;

This may not tell you anything new, but it's where I'd start.