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

by

Troubleshooting Guide: SQL Server Error 5204 — “Could not find allocation unit ID %I64d”

Why This Error Happens

This error occurs when SQL Server’s internal metadata tracking for allocation units becomes inconsistent with the actual data pages stored in the database, typically due to database corruption caused by improper shutdowns, storage subsystem failures, or disk-level bit rot. It can also surface after a failed or interrupted index rebuild, a hardware-level I/O error that silently corrupted system catalog tables, or antivirus/backup software locking database files mid-write. In nearly all cases, this points to physical corruption in the sys.allocation_units system table or the data/index pages it references, rather than a permissions or configuration issue.

Step-by-Step Resolution Guide

Step 1: Run DBCC CHECKDB to Confirm and Scope the Corruption

  • Open SQL Server Management Studio (SSMS) or sqlcmd on Windows Server 2025 / Windows 11.
  • Execute: DBCC CHECKDB ('YourDatabaseName') WITH NO_INFOMSGS, ALL_ERRORMSGS;
  • Review the output for specific object IDs, page IDs, and allocation unit IDs flagged as corrupt.
  • Check the SQL Server error log (C:\Program Files\Microsoft SQL Server\MSSQL17.MSSQLSERVER\MSSQL\Log\ERRORLOG) for correlating I/O or storage errors around the same timestamp.

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

  • Identify the most recent clean full backup using: RESTORE HEADERONLY FROM DISK = 'D:\Backups\YourDatabase.bak';
  • Restore to a new database name first to validate integrity: RESTORE DATABASE YourDatabaseName_Test FROM DISK = 'D:\Backups\YourDatabase.bak' WITH MOVE 'logical_name' TO 'D:\Data\YourDatabase_Test.mdf', MOVE 'logical_name_log' TO 'D:\Data\YourDatabase_Test.ldf';
  • Run DBCC CHECKDB against the restored copy to confirm it is error-free before cutting over production traffic.

Step 3: Use Repair Options as a Last Resort

  • Set the database to single-user mode: ALTER DATABASE YourDatabaseName SET SINGLE_USER WITH ROLLBACK IMMEDIATE;
  • Run the least destructive option first: DBCC CHECKDB ('YourDatabaseName', REPAIR_REBUILD);
  • If allocation errors persist, escalate to: DBCC CHECKDB ('YourDatabaseName', REPAIR_ALLOW_DATA_LOSS); — note this may delete corrupted rows/pages permanently.
  • Return the database to multi-user mode: ALTER DATABASE YourDatabaseName SET MULTI_USER;

Step 4: Check Underlying Storage Health

  • On Windows Server 2025, run chkdsk D: /F /R (adjust drive letter) during a maintenance window to rule out disk-level file system errors.
  • Review Windows Event Viewer under Windows Logs > System for Event IDs 7, 11, or 51 (disk/controller errors) that may indicate failing hardware.
  • Confirm your storage driver and firmware are up to date via Windows Update or the vendor’s management utility.

Alternative or Verification Steps

  • After repair or restore, re-run DBCC CHECKDB ('YourDatabaseName') WITH NO_INFOMSGS, ALL_ERRORMSGS; and confirm zero allocation or consistency errors are returned.
  • If corruption recurs frequently, consider running DBCC CHECKDB on a scheduled Agent job and enabling Page Verification (CHECKSUM) via ALTER DATABASE YourDatabaseName SET PAGE_VERIFY CHECKSUM; to catch future I/O corruption earlier.