Quick Scan Report – Large Differentials

What this check looks for

Differential backup sizes against the size of the full backup they are based on. Differentials smaller than 500 MB are ignored, because a small differential is not worth reporting however it compares.

The threshold is configurable: where a DBHealthHistory database exists with a Settings table, the check reads its value from there, so an instance with genuinely large databases can raise the floor rather than living with a finding that never clears.

Why it matters

A differential backup contains every extent changed since the last full backup, and it only ever grows. It is not incremental: each one is cumulative from the same base, so Monday’s differential is smaller than Tuesday’s, which is smaller than Wednesday’s.

That is the design, and it is deliberate, because it means a restore needs only the full plus one differential rather than a chain of incrementals. The trade is that the differential keeps growing until the next full backup resets the base.

Past roughly half the size of a full backup, the arithmetic turns against you:

• Backup time. A differential at 80 percent of a full takes roughly 80 percent as long to take, so you are paying nearly the cost of a full backup and getting less.
• Storage. You are keeping fulls and near-full differentials.
• Restore time. A restore is the full, then the differential, then the log backups. A large differential adds directly to how long the restore takes, and restore time is the number that matters during an incident.

And the cause is usually something worth knowing about, because a differential grows in proportion to how much of the database has changed:

• The full backup is too infrequent. Weekly fulls with daily differentials on an active database means Saturday’s differential covers six days of change.
• Index maintenance. This is the one people miss. A rebuild rewrites every page of an index, and every one of those pages is now “changed”. A full index rebuild the night after a full backup can take the next differential to nearly the size of the database, without a single row of data having changed.
• A large data load or archive that touched most of the table.

The index maintenance interaction is worth pausing on, because it explains most surprising cases. If the maintenance window runs index rebuilds and the full backup runs weekly, the differentials after the rebuild night are enormous for reasons that have nothing to do with the application.

How to confirm it yourself

Differential sizes against their base, over the last month:

WITH backups AS (
    SELECT bs.[database_name],
           bs.[type],
           bs.[backup_start_date],
           CAST(bs.[backup_size]            / 1048576.0 AS DECIMAL(12,1)) AS [size_mb],
           CAST(bs.[compressed_backup_size] / 1048576.0 AS DECIMAL(12,1)) AS [compressed_mb]
      FROM msdb.dbo.backupset AS bs WITH (NOLOCK)
     WHERE bs.[backup_start_date] > DATEADD(DAY, -30, GETDATE())
       AND bs.[type] IN ('D', 'I')
       AND bs.[is_snapshot] = 0
)
SELECT d.[database_name],
       d.[backup_start_date]                                        AS [diff_taken],
       d.[size_mb]                                                  AS [diff_mb],
       f.[size_mb]                                                  AS [full_mb],
       CAST(d.[size_mb] * 100.0 / NULLIF(f.[size_mb], 0) AS DECIMAL(5,1)) AS [pct_of_full]
  FROM backups AS d
 CROSS APPLY (SELECT TOP (1) [size_mb]
                FROM backups AS b
               WHERE b.[database_name] = d.[database_name]
                 AND b.[type] = 'D'
                 AND b.[backup_start_date] <= d.[backup_start_date]
               ORDER BY b.[backup_start_date] DESC) AS f
 WHERE d.[type] = 'I'
 ORDER BY [pct_of_full] DESC;

The definitive answer, without waiting for the next backup, from SQL Server 2017 onwards:

SELECT DB_NAME([database_id])                                             AS [database_name],
       [file_id],
       [modified_extent_page_count] * 8 / 1024                            AS [changed_mb],
       [total_page_count] * 8 / 1024                                      AS [total_mb],
       CAST([modified_extent_page_count] * 100.0
            / NULLIF([total_page_count], 0) AS DECIMAL(5,1))              AS [pct_changed]
  FROM sys.dm_db_file_space_usage WITH (NOLOCK);

modified_extent_page_count is exactly what the next differential will contain. Above about 50 percent, a full backup is cheaper than the differential. That query is also the basis of a smarter backup job: take a differential normally, and a full when the percentage crosses your threshold.

How to fix it

Take full backups more often, or take them at the right moment.

1. Increase the full backup frequency. Weekly fulls with daily differentials suits a database that changes slowly. On an active one, daily fulls with hourly differentials, or simply daily fulls and log backups, is often both simpler and smaller.
2. Put the full backup after the index maintenance, not before. This is the single highest-value change on this page and it costs nothing. If maintenance runs Saturday night, run the full backup Sunday morning. The rebuilt pages go into the full rather than into every differential for the following week.
3. Reconsider the index maintenance itself. A job that rebuilds everything every night is changing the whole database every night, which makes differentials pointless. A threshold-aware script rebuilds far less, and that has its own check.
4. Use the DMV to decide dynamically. Take a full when modified_extent_page_count crosses your threshold rather than on a fixed day. Ola Hallengren’s DatabaseBackup supports this with @ChangeBackupType and @ModificationLevel.
5. Compress, if you are not already. It does not change the proportion, and it reduces both storage and backup time:

BACKUP DATABASE [YourDatabase]
  TO DISK = N'...' WITH DIFFERENTIAL, COMPRESSION, CHECKSUM;

Check nothing else is resetting the base. An ad hoc full backup or a VM backup without COPY_ONLY moves the differential base without telling you, and both have their own checks.

How long it takes

About half an hour to change the schedule. Moving the full backup to after the maintenance window is usually the whole fix.

Related reports

Related checks

Frequently asked questions

Are differentials incremental? No. Each contains everything changed since the last full backup, so they grow. That is what makes a restore simple and what causes this finding.

What percentage is too large? Around 50 percent is where a full backup becomes the better deal. The modified_extent_page_count query gives you the number before you take the backup.

Our differential jumped overnight. Look at what ran. An index rebuild is the usual answer, and it changes pages without changing data.

Should we stop taking differentials? On a database where they grow large quickly, daily fulls plus log backups is often simpler and restores faster. Differentials earn their place on large databases that change slowly.