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

by

Troubleshooting Guide: SQL Server Error 5225 – Ghost Record Cleanup Failure on LOB Pages

Why This Error Happens

Error 5225 occurs when SQL Server’s ghost cleanup task is unable to physically remove deleted (ghost) records from a Large Object (LOB) page because a long-running transaction or a query on a readable secondary replica still holds a reference to that version of the data. This is common in Always On Availability Group topologies where the ghost cleanup boundary on the primary is held back by the oldest active transaction on a readable secondary. It can also indicate underlying corruption on the LOB page itself if the condition persists even after all blocking transactions have closed.

Step-by-Step Resolution Guide

Step 1: Identify the Blocking Transaction or Replica

  • Run SELECT * FROM sys.dm_hadr_database_replica_states on the primary to check the secondary_lag_seconds and last_hardened_time for each replica.
  • On the secondary replica, execute DBCC OPENTRAN to identify any long-running read transactions holding snapshot isolation locks.
  • Check sys.dm_exec_sessions and sys.dm_tran_active_snapshot_database_transactions on the secondary for sessions with an old transaction_sequence_num.

Step 2: Check the Current Ghost Cleanup Boundary

  • Query SELECT * FROM sys.dm_db_log_stats(DB_ID()) to review log_truncation_holdup_reason; if it returns AVAILABILITY_REPLICA, this confirms the secondary is holding back cleanup.
  • Use DBCC TRACEON(3604) followed by DBCC PAGE (in a test environment) with the file/page ID from the error message (%d:%d) to inspect the LOB page allocation unit.
  • Cross-reference the oldest_active_transaction_id on the secondary using sys.dm_tran_database_transactions to correlate with the stalled boundary.

Step 3: Terminate or Resolve the Long-Running Secondary Query

  • On the secondary replica, identify the offending SPID using sys.dm_exec_requests and cross-apply sys.dm_exec_sql_text(sql_handle).
  • If the session is safe to terminate, run KILL <session_id> on the secondary to release the snapshot and unblock the ghost cleanup boundary on the primary.
  • Restart the SQL Server Agent job “Ghost Cleanup” or wait for the automatic background task to run (default cycle: every 5–10 seconds) once the blocking session is cleared.

Step 4: Run Consistency Checks if the Error Persists

  • Execute DBCC CHECKDB('YourDatabaseName') WITH NO_INFOMSGS, ALL_ERRORMSGS to rule out physical corruption on the LOB page referenced in the error (file ID : page ID).
  • If corruption is confirmed, restore the affected database or table from the last known good backup rather than attempting REPAIR_ALLOW_DATA_LOSS on LOB pages, as this can cause data truncation.
  • Apply the latest Cumulative Update for SQL Server 2025 via Windows Update or the Microsoft Update Catalog, as ghost cleanup timing bugs on AG secondaries are periodically patched.

Step 5: Adjust Secondary Replica Read Workload Settings

  • On Windows Server 2025, open SQL Server Configuration Manager and verify the AG listener port isn’t conflicting with other services (check netstat -ano | findstr <port>).
  • Reduce the isolation level burden on the secondary by setting shorter-lived read transactions, or adjust the application’s connection timeout in appsettings.json / connection string (Application Intent=ReadOnly).

Alternative or Verification Steps

  • Verify the fix by re-running DBCC CHECKDB on the affected database and confirming Error 5225 no longer appears in the SQL Server Error Log (Get-Content "C:\Program Files\Microsoft SQL Server\MSSQL17.MSSQLSERVER\MSSQL\Log\ERRORLOG" in PowerShell on Windows Server 2025).
  • As an alternative, if the issue is isolated to one readable secondary, temporarily remove it from the Availability Group read-routing list to confirm ghost cleanup completes normally on the primary before re-adding it.