Ola Hallengren MaintenanceSolution - Differential - the value for @ModificationLevel is not supported

Question

I just installed this great tool, mostly it's working fine, just that if I include for a differential backup the parameter @ModificationLevel = 50 (or anything else) the action fails:

Msg 50000, Level 16, State 1, Procedure DatabaseBackup, Line 2268 The value for the parameter @ModificationLevel is not supported.

Example:

EXECUTE [dbo].[DatabaseBackup]
@Databases = 'ALL_DATABASES', @CleanupTime = 2160, @Compress = 'Y', 
@Directory = '\\cem01nas2\sql_backups',
@BackupType = 'DIFF', @ChangeBackupType = 'Y', @ModificationLevel = 50 
GO

Gives the error, but if I remove the @ModificationLevel parameter it works. Any ideas?

Answer 1

This indicates that you are hitting the validation (taken from Ola Hallengren's site here)

  IF @ModificationLevel IS NOT NULL AND NOT EXISTS(SELECT * FROM sys.all_columns WHERE object_id = OBJECT_ID('sys.dm_db_file_space_usage') AND name = 'modified_extent_page_count')
  BEGIN
    INSERT INTO @Errors ([Message], Severity, [State])
    SELECT 'The value for the parameter @ModificationLevel is not supported.', 16, 1
  END

  IF @ModificationLevel IS NOT NULL AND @ChangeBackupType = 'N'
  BEGIN
    INSERT INTO @Errors ([Message], Severity, [State])
    SELECT 'The value for the parameter @ModificationLevel is not supported.', 16, 2
  END

  IF @ModificationLevel IS NOT NULL AND @BackupType <> 'DIFF'
  BEGIN
    INSERT INTO @Errors ([Message], Severity, [State])
    SELECT 'The value for the parameter @ModificationLevel is not supported.', 16, 3
  END

Please check the conditions and also keep in mind the @ModificationLevel is the difference needed for a Full backup to occur. If there is not enough difference it won't do the Full backup.

IF @ChangeBackupType = 'Y'
    BEGIN
      IF @CurrentBackupType = 'LOG' AND @CurrentRecoveryModel IN('FULL','BULK_LOGGED') AND @CurrentLogLSN IS NULL AND @CurrentDatabaseName <> 'master'
      BEGIN
        SET @CurrentBackupType = 'DIFF'
      END
      IF @CurrentBackupType = 'DIFF' AND (@CurrentDatabaseName = 'master' OR @CurrentDifferentialBaseLSN IS NULL OR (@CurrentModifiedExtentPageCount * 1. / @CurrentAllocatedExtentPageCount * 100 >= @ModificationLevel))
      BEGIN
        SET @CurrentBackupType = 'FULL'
      END
    END

Answer 2

You haven't specified what version of SQL Server, but if you're using an earlier version of SQL Server, the column modified_extent_page_count doesn't exist in sys.dm_db_file_space_usage.

This will cause the error you're seeing on an older version of SQL Server because the field used to calculate the differential amount doesn't exist. This is the first of the validations in the source code, although the error message is a little misleading.

NOTE: The documentation says SQL Server 2016 SP2 is the earliest version, however, I tested against a SQL Server 2014 SP3 instance and this column was present. It was missing in a SQL Server 2012 SP4 instance.

You can confirm if this is the issue by running this query - SELECT * FROM sys.dm_db_file_space_usage - and checking if the modified_extent_page_count column exists.