How to fix Error 4221 on Windows Server 2025 / Windows 11

by

Troubleshooting Guide: Error 4221 – Login to Read-Secondary Failed Due to Row Versioning Timeout

Why This Error Happens

This error occurs because the secondary replica was restarted or recycled while transactions were still in-flight on the primary, leaving a gap in the persisted version store required for snapshot isolation reads. SQL Server 2025 requires the replica to rebuild and align its row versioning chain (HADR_DATABASE_WAIT_FOR_TRANSITION_TO_VERSIONING) before it can safely serve read-only connections, and until that transition completes, logins are blocked. This is commonly triggered by aggressive failover automation, delayed redo on the secondary, or insufficient tempdb/version store capacity to catch up quickly.

Step-by-Step Resolution Guide

Step 1: Confirm the Replica Health and Wait Type

  • Run the following on the secondary replica to confirm the wait type is active:
  SELECT * FROM sys.dm_os_waiting_tasks WHERE wait_type = 'HADR_DATABASE_WAIT_FOR_TRANSITION_TO_VERSIONING';
  • Check sys.dm_hadr_database_replica_states for synchronization_state and is_primary_replica to confirm the replica is in SYNCHRONIZED or SYNCHRONIZING state.
  • Validate redo queue size via redo_queue_size column — large values indicate the replica is still catching up.

Step 2: Increase Tempdb and Version Store Capacity

  • Navigate to SQL Server Configuration Manager > SQL Server Services > confirm tempdb data files are on fast storage (NVMe/SSD).
  • Increase tempdb file count to match logical CPU cores (edit via SSMS or):
  ALTER DATABASE tempdb ADD FILE (NAME = tempdev2, FILENAME = 'D:\SQLData\tempdb2.ndf', SIZE = 2048MB, FILEGROWTH = 512MB);
  • Monitor sys.dm_tran_version_store_space_usage to ensure version store isn’t being starved by long-running transactions on the primary.

Step 3: Adjust Connection Retry and Read-Only Routing Settings

  • In your application connection string, increase Connect Timeout and enable retry logic to tolerate the versioning transition window (typically 30–120 seconds).
  • On Windows Server 2025, verify the Always On Availability Group listener settings using:
  Get-ClusterResource | Where-Object {$_.ResourceType -eq "SQL Server Availability Group"}
  • Avoid forcing immediate reads on a freshly restarted secondary — delay application-level read redirection until the replica reports HEALTHY in sys.dm_hadr_availability_replica_states.

Step 4: Restart the Secondary Replica Cleanly (If Stuck)

  • If the wait persists beyond 5–10 minutes with no progress, manually suspend and resume data movement for the affected database:
  ALTER DATABASE [YourDB] SET HADR SUSPEND;
  ALTER DATABASE [YourDB] SET HADR RESUME;
  • This forces SQL Server to reinitialize the version store transition cleanly rather than waiting indefinitely.

Alternative or Verification Steps

  • Verify the fix by running a test read-only connection against the secondary using ApplicationIntent=ReadOnly in the connection string and confirming successful login without delay.
  • If the issue recurs frequently, consider disabling automatic failover temporarily and switching to manual failover mode via SSMS > Availability Group Properties > Failover Mode, to prevent replica recycling during high transaction load.