DatabaseBackup - @MinBackupSizeForMultipleFiles ignored when backing up log of an AG database from a secondary node where the availability group backup preference is also secondary.

[R874720]

Description of the issue

When runnining a command such as:

EXECUTE BackupDatabase
    @Databases = N'AVAILABILITY_GROUP_DATABASES'
   ,@Directory = N'\\BackupDirectory'
   ,@BackupType = N'Log'
   ,@NumberOfFiles = 4
   ,@MinBackupSizeForMultipleFiles = 1024

on a secondary node, with availability group backup preference also set as secondary, the number of files used to create the transaction log backup will always equal @NumberOfFiles, even if the size of the log to be backed up is less than @MinBackupSizeForMultipleFiles.

I believe this is due to the following statement at line 2685:

      SELECT @CurrentLastLogBackup = log_backup_time,
             @CurrentLogSizeSinceLastLogBackup = log_since_last_log_backup_mb
      FROM sys.dm_db_log_stats (DB_ID(@CurrentDatabaseName))

which returns NULL for log_since_last_log_backup_mb when run from a secondary node.

This in turn leads to the logic in the statement at line 2707 executing the highlighted CASE clause incorrectly:

WHEN @NumberOfFiles > 1 AND (BackupSize >= MinBackupSizeForMultipleFiles OR MinBackupSizeForMultipleFiles IS NULL OR BackupSize IS NULL) THEN @NumberOfFiles

as @NumberOfFiles is = 4 and BackupSize is NULL.

The net result is that for any log backups run on a secondary node of an availability group where the availability group backup preference is secondary, the number of files used to backup the log will always equal the value of @NumberOfFiles, regardless of the value of @MinBackupSizeForMultipleFiles.

SQL Server version and edition Microsoft SQL Server 2022 (RTM) - 16.0.1000.6 (X64) Oct 8 2022 05:58:25 Copyright (C) 2022 Microsoft Corporation Enterprise Edition: Core-based Licensing (64-bit) on Windows Server 2022 Standard 10.0 (Build 20348: ) (Hypervisor)

Version of the script 2022-12-03 17:23:44

[olahallengren]

I will test this, but I believe this is a limitation in sys.dm_db_log_stats.

[olahallengren]

I checked the documentation and log_since_last_log_backup_mb returns NULL on secondary replicas. https://learn.microsoft.com/en-us/sql/relational-databases/system-dynamic-management-views/sys-dm-db-log-stats-transact-sql?view=sql-server-ver16

Given this limitation, I am not sure what I could do other than using @NumberOfFiles.