Troubleshooting Guide: Error 4223 — Log Backup Fails with MAXVLFCOUNT Option on Hadron Secondaries

Why This Error Happens

Error 4223 occurs because the MAX_VLF_COUNT option for BACKUP LOG is only valid when executed against the primary replica of an Always On Availability Group (internally referred to as “Hadron” in SQL Server). When this backup option is issued against a secondary replica, SQL Server 2025 rejects the command because VLF (Virtual Log File) count enforcement logic requires write access to transaction log metadata that secondaries do not own. This is a design limitation rather than a corruption or permissions issue — it stems from attempting to pair a primary-only option with a read-only replica backup operation.

Step-by-Step Resolution Guide

Step 1: Identify Where the Backup Job Is Running

• Open SQL Server Management Studio (SSMS) and connect to the Availability Group listener or the specific replica instance.
• Run the following query to confirm replica role:

  SELECT role_desc FROM sys.dm_hadr_availability_replica_states 
  WHERE is_local = 1;

• If the result returns SECONDARY, this confirms the root cause of Error 4223.

Step 2: Remove MAXVLFCOUNT from Secondary Backup Scripts

• Edit the backup job step (via SQL Server Agent or maintenance plan) running on the secondary replica.
• Remove the MAX_VLF_COUNT = n clause from the BACKUP LOG statement, for example:

  BACKUP LOG [YourDatabase] 
  TO DISK = 'D:\Backups\YourDatabase_log.trn';

• Save and redeploy the job on all secondary-hosted backup schedules.

Step 3: Relocate MAXVLFCOUNT Logic to the Primary Replica

• If VLF count control is required, schedule a separate job that runs BACKUP LOG ... WITH MAX_VLF_COUNT exclusively against the primary replica.
• Use sys.fn_hadr_backup_is_preferred_replica to dynamically route backup jobs only when the current node is primary:

  SELECT sys.fn_hadr_backup_is_preferred_replica('YourDatabase');

• Wrap the MAX_VLF_COUNT logic in a conditional check so it only executes when this function returns 1 and the replica role is PRIMARY.

Step 4: Validate Backup Preference Routing in the Availability Group

• Open SSMS → Availability Groups → Properties → Backup Preferences.
• Confirm the “Backup Preference” setting (e.g., Primary, Secondary only, or Prefer Secondary) aligns with where your MAX_VLF_COUNT jobs are configured to run.
• Adjust routing so VLF-count-enforced backups always target the primary, regardless of AG failover state.

Alternative or Verification Steps

• Re-run the backup job manually on the primary replica using BACKUP LOG ... WITH MAX_VLF_COUNT and confirm it completes without Error 4223.
• If failovers are frequent, implement a wrapper stored procedure that checks replica role at runtime and conditionally omits MAX_VLF_COUNT when executing on a secondary, ensuring uninterrupted log backups during automatic failover events.