Compression setting not effective for differential backup maintenance plan?

Question

We are taking backups (Full, Differential, and Transaction Log) of databases using the maintenance plans feature in SQL Server.

While taking the backup, I have chosen the compress option to compress the backup files, and I have enabled "backup compression default" also.

Full backup maintenance plans are compressing the backup file; file size is 4 times less than the database size. But when we comes to differential backup, I think the compression setting is not working.

I have observed today's backup differential file. Yesterday 4 tables in a database got populated with 10GB of data each, so differential backup file size crossed 40GB. I am certain these were the only changes, because it is a fresh database, and there were no maintenance activities run on that day.

The .ldf file size grew to 500 GB on the day the backup was performed. This might cause an issue for big size, but database size (.mdf) is less than 50GB.

Please look at the below image for additional info:

I want to confirm that compression will not work for differential backups, or perhaps there is something else I missed?

If it does not work, based on Paul Randal's database percent change script, I may decide to take full or differential backup.

Version

select @@version;

Microsoft SQL Server 2012 (SP1) - 11.0.3156.0 (X64) 
    May  4 2015 18:48:09 
    Copyright (c) Microsoft Corporation
    Standard Edition (64-bit) on Windows NT 6.2 <X64> (Build 9200: ) (Hypervisor)

Size Info

select type, 
       backup_size,
       compressed_backup_size
from msdb..backupset
where backup_start_date >= '20170927' 
and database_name = 'yourDB'

type    backup_size      compressed_backup_size
I       213,473,351,680  42,147,293,093

Answer 1

_{Community wiki answer:}

Checking compression

You can check if your backup is compressed or not. There are a few methods in How can I tell if a SQL Server backup is compressed?.

You can also check the value of compressed_backup_size and backup_size column of the backupset table in msdb. For example:

SELECT
    database_name,
    type,
    PctCompression =
        CONVERT(NUMERIC(20,2),100 - (compressed_backup_size / backup_size) * 100)
FROM msdb.dbo.backupset
WHERE ...

The update to your question shows that the differential backup was indeed compressed.

Backup larger than expected

That 10GB of data could be spread over a lot of pages, and those pages could total a great deal more than the 10GB you inserted (especially with poor clustered index choices). Note also that differential backups operate at the extent level, so any change to any page within an 8-page extent will result in the whole extent being backed up.

Check the log_reuse_wait_desc after doing log backup or use dbcc opentran. If there is an open transaction since yesterday, your full and differential backups will be larger due to the included active log.

There may be no open transaction now, but if a long-running transaction was active at the time of differential backup, the backup file size will be more than expected.

The fact that your log grew to 500GB (and you take regular log backups) means the log could not be cleared for long time due to something (probably an open transaction). All the log associated with that transaction was added to the differential backup.

If you want to check if it's so, try to restore your database into another (new) database, perhaps on another server. This way you will do 2 things:

1. You'll verify your full and your diff backups; and
2. You'll see how much time your differential will take to restore.

If the differential takes a long time (e.g. 3+ hours) it indicates a lot of time was spent undoing the effects of a long-running open transaction.