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

by

Troubleshooting Guide: Error 5234 — DBCC SHRINKDATABASE File Skipped During Space Allocation Adjustment

Why This Error Happens

Error 5234 typically occurs when SQL Server 2025 cannot acquire the necessary locks on a database file because another process—such as an active transaction, an index rebuild, or a concurrent backup—is holding a conflicting lock. This can also happen when the data file is close to its maximum configured size, has autogrowth disabled, or resides on storage with insufficient free space, preventing SQL Server from adjusting the allocation map. In some cases, file system-level permission restrictions on the Windows Server 2025 host (especially with newly enforced security defaults) block the SQL Server service account from modifying file metadata.

Step-by-Step Resolution Guide

Step 1: Check for Blocking Transactions and Active Sessions

  • Run sp_who2 or query sys.dm_exec_requests to identify sessions holding locks on the target database.
  • Look specifically for long-running transactions, open index maintenance jobs, or replication processes.
  • Terminate or wait out blocking sessions using KILL <session_id> only after confirming it is safe to do so.

Step 2: Verify File Growth Settings and Disk Space

  • Open SSMS, navigate to Database Properties > Files, and confirm “Autogrowth/Maxsize” is not restricted to a value smaller than required.
  • Check the underlying disk volume on Windows Server 2025 using Get-PSDrive in PowerShell to confirm sufficient free space exists.
  • If the file has hit MAXSIZE, increase it manually:
  • ALTER DATABASE [YourDB] MODIFY FILE (NAME = 'YourFileLogicalName', MAXSIZE = UNLIMITED);

Step 3: Validate File System Permissions for the SQL Server Service Account

  • Navigate to the data file directory (commonly C:\Program Files\Microsoft SQL Server\MSSQL16.MSSQLSERVER\MSSQL\DATA).
  • Right-click the .mdf/.ldf files, go to Properties > Security, and confirm the SQL Server service account (e.g., NT Service\MSSQLSERVER) has Full Control.
  • Reapply permissions if needed using icacls:
  • icacls "D:\SQLData\YourDB.mdf" /grant "NT SERVICE\MSSQLSERVER:(F)"

Step 4: Retry Shrink with Lower Load and Explicit File Targeting

  • Run the shrink operation during low-activity hours, targeting the specific file ID reported in the error:
  • DBCC SHRINKFILE (N'YourFileLogicalName', TRUNCATEONLY);
  • Avoid using DBCC SHRINKDATABASE broadly; shrink problematic files individually to reduce lock contention.

Step 5: Check for Corruption on the Target File

  • Run DBCC CHECKDB('YourDB') WITH NO_INFOMSGS, ALL_ERRORMSGS; to rule out underlying corruption preventing allocation changes.
  • If corruption is found, restore from a verified backup rather than continuing shrink operations.

Alternative or Verification Steps

  • After applying fixes, re-run DBCC SHRINKDATABASE (YourDB) and confirm no Error 5234 appears in the output or SQL Server Error Log (EXEC xp_readerrorlog 0, 1, N'5234';).
  • If the issue persists, consider rebuilding indexes first with ALTER INDEX ALL ON YourTable REBUILD; to release fragmented space naturally, then retry the shrink as an alternative path.