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

by

Troubleshooting Guide: Error 5224 — Empty Large Object Page Could Not Be Deallocated

Why This Error Happens

This error occurs when SQL Server’s deallocation routine encounters a LOB (Large Object) page that is marked as empty but cannot be properly unlinked from the allocation structures (IAM or PFS pages) due to internal inconsistency. The root cause is typically database corruption resulting from an unexpected shutdown, storage subsystem failure, or a bad sector affecting the page allocation metadata. In some cases, this is triggered by antivirus or backup software locking the .mdf/.ndf files while SQL Server attempts to write to them.

Step-by-Step Resolution Guide

Step 1: Run DBCC CHECKDB to Assess Damage

  • Open SQL Server Management Studio (SSMS) and connect to the affected instance.
  • Execute DBCC CHECKDB ('YourDatabaseName') WITH NO_INFOMSGS, ALL_ERRORMSGS;
  • Review the output for allocation errors specifically tied to LOB pages (text, ntext, image, varchar(max), or XML data types).
  • Note the exact page IDs and object IDs referenced in the error for targeted repair.

Step 2: Check Storage Hardware and Windows Event Logs

  • Open Event Viewer on Windows Server 2025 / Windows 11 and navigate to Windows Logs > System.
  • Look for Disk, NTFS, or Storport errors around the same timestamp as the SQL error.
  • Verify the health of the underlying storage using Get-PhysicalDisk | Select FriendlyName, HealthStatus in PowerShell.
  • If using virtualized storage (Hyper-V/VMware), confirm the virtual disk isn’t thin-provisioned and running out of space.

Step 3: Restore From a Known-Good Backup (Preferred Fix)

  • Navigate to your backup repository (local path or network share, e.g., D:\SQLBackups\).
  • Restore the database using RESTORE DATABASE YourDatabaseName FROM DISK = 'D:\SQLBackups\YourDatabaseName.bak' WITH REPLACE;
  • This avoids data loss associated with repair commands that may discard corrupted LOB pages entirely.

Step 4: Run Targeted Repair if No Backup Exists

  • Set the database to single-user mode: ALTER DATABASE YourDatabaseName SET SINGLE_USER WITH ROLLBACK IMMEDIATE;
  • Execute DBCC CHECKDB ('YourDatabaseName', REPAIR_ALLOW_DATA_LOSS); — note this is a last resort as it may remove the corrupted LOB rows entirely.
  • Set the database back to multi-user mode: ALTER DATABASE YourDatabaseName SET MULTI_USER;

Step 5: Exclude SQL Server Files from Antivirus/Backup Scanning

  • Open Windows Security or your third-party AV console.
  • Add exclusions for the SQL Server data directory, typically C:\Program Files\Microsoft SQL Server\MSSQL17.MSSQLSERVER\MSSQL\DATA\.
  • Exclude .mdf, .ldf, and .ndf file extensions from real-time scanning to prevent file-lock conflicts during write operations.

Alternative or Verification Steps

  • Re-run DBCC CHECKDB ('YourDatabaseName') WITH NO_INFOMSGS, ALL_ERRORMSGS; after remediation — a clean pass with zero allocation errors confirms the fix.
  • If corruption persists after repair, consider migrating the affected table’s data to a new table using SELECT INTO and dropping the original, as this can isolate and eliminate damaged LOB page chains entirely.