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

by

Why This Error Happens

Error 5240 occurs when SQL Server 2025 detects a concurrency conflict on a specific database file—typically because two shrink operations (DBCC SHRINKFILE, DBCC SHRINKDATABASE, or an Autoshrink job) are attempting to target the same File ID simultaneously. It can also trigger if the file is already at its minimum possible size (effectively “empty” of reclaimable space), or if a previous shrink process crashed and left an internal lock flag active on the file metadata. This is a logical locking issue within SQL Server’s internal engine rather than a Windows OS-level permission failure.

Step-by-Step Resolution Guide

Step 1: Identify Active Shrink Processes

  • Open SQL Server Management Studio (SSMS) and run a query against sys.dm_exec_requests to check for existing commands with the command column showing DbccFilesCompact or DbccSpaceReclaim.
  • Execute: SELECT session_id, command, percent_complete FROM sys.dm_exec_requests WHERE command LIKE '%SHRINK%';
  • If a session is found, do not kill it immediately; allow it to finish, as killing a shrink mid-process can cause database inconsistency.

Step 2: Verify Autoshrink Configuration Conflicts

  • Check if the AUTO_SHRINK database property is enabled, as this background process often fights with manual DBA-initiated shrinks.
  • Navigate to SSMS > Database Properties > Options > Automatic > Auto Shrink, and set this to False.
  • Alternatively, run: ALTER DATABASE [YourDB] SET AUTO_SHRINK OFF;

Step 3: Disable and Isolate the Shrink Operation

  • Stop any SQL Server Agent Jobs scheduled to run maintenance plans that include “Shrink Database” tasks, as these often queue up during peak hours causing file locks.
  • Open SQL Server Agent > Jobs in SSMS and disable any job containing the Shrink task type temporarily.
  • Attempt the shrink again using a single-threaded approach: DBCC SHRINKFILE (N'YourFileLogicalName', 0);

Step 4: Restart the SQL Server Service to Clear Locks

  • If the File ID remains locked with no active process found in Step 1 (orphaned lock), restart the service to clear the internal engine state.
  • Open Services.msc on Windows Server 2025, locate “SQL Server (MSSQLSERVER),” right-click, and select Restart.
  • Note: This requires a maintenance window as it will drop all active connections.

Alternative or Verification Steps

  • Verify the Fix: Run DBCC SHRINKFILE again immediately after the restart; if the file size (reported in sys.database_files) decreases without throwing Error 5240, the lock has been cleared successfully.
  • Alternative Solution: If the file is genuinely “empty” and cannot be shrunk further, this is expected behavior (not a bug); verify the size vs. used_pages in sys.database_files to confirm the file is already at its minimum logical size.