Sql-server – DIFFs promoted to FULLs

backupola-hallengrensql serversql-server-2012

We use Ola Hallengren's DatabaseBackup stored procedure to backup a load of SharePoint databases on a SQL Server 2012 instance into Azure blob storage. We have been doing this for quite a while without any issues. However, for the last 6 weeks our DIFFs are randomly being promoted to FULL and we cannot find out why.

This is the output from the agent step

BACKUP DATABASE [Database] TO URL = N'https://strorgage.blob.core.windows.net/server/instance/Database/2020/11/diff/Database_FULL_20201105_200000.bak' 
WITH NO_CHECKSUM, COMPRESSION, CREDENTIAL = N'*storeageaccountname*'

If you have a look at the generted URL you will note that the procedures is storing in the DIFF directory, but creating a FULL backup file.

https://strorgage.blob.core.windows.net/server/instance/Database/2020/11/diff/Database_FULL_20201105_200000.bak
                                                                        --^           --^

DatabaseBackup (Ola proc) is from 2019-06-14 so it needs an upgrade to be fair, but it has worked well for over 18 months.

We do not call Ola code directly as we have a small wrapper procedure which builds up the virtual path name for Azure, but essentially this is how we call Ola's code.

This is an issue were for some unknown reason DIFF backups are being promoted to FULL, this causes petabytes of Azure blob backups instead of gigabytes – each day.

EXECUTE dbo.DatabaseBackup
    @Database = @DatabaseName,
    @URL = @BackupPath,
    @Credential = @StorageAccount,
    @BackupType = @backupType,
    @Compress = @Compression,
    @LogToTable = 'Y',
    @ChangeBackupType = 'Y',
    @Updateability = @DatabaseReadOnlyState,
    @DirectoryStructure = NULL,
    @AvailabilityGroupDirectoryStructure = NULL

Do you have any thoughts on that?

Best Answer

You say that this promotion of DIFF to FULL backups is "random" but I'd wager you can find a connection between this activity and data churn (or index maintenance) in the database itself.

Because you're using ChangeBackupType='Y', the backup job is looking at sys.dm_db_file_space_usage to see how much of the database has been changed, and performs a FULL backup if it exceeds a threshold (I'm having difficulty discerning the default threshold from the source code). You can change that threshold by adjusting the ModificationLevel parameter, which is a percentage. From the documentation

ModificationLevel
Specify a percentage when a differential backup will be changed to a full backup. This option can only be used together with @ChangeBackupType = 'Y'. DatabaseBackup checks allocated_extent_page_count and modified_extent_page_count in sys.dm_db_file_space_usage to calculate how much of a database that has been modified.

Related Question