2

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.

enter image description here

Finally this one went down in size, further down next day and then up the that place it was the first day.

enter image description here


In this case:

enter image description here

On restoring, the file the size increases quite a bit:

enter image description here

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.

  • 1
    If you aren't expecting much to happen on the DB, why rebuild indexes daily? Are you rebuilding all indexes? That is effectively 100% data change on a daily basis (in regard to re-writing the same data every day). – AMtwo Sep 22 '16 at 12:49
2

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.

0

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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.