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

by

Troubleshooting Guide: SQL Server Error 5054 — Worktable IAM Chain Cleanup Failure

Why This Error Happens

This error occurs when SQL Server attempts to shrink or remove a tempdb data file while active worktables, work files, or internal sort/hash structures are still allocated in tempdb, preventing the engine from cleaning up the Index Allocation Map (IAM) chains. It is typically triggered by concurrent query activity (such as sorts, hash joins, cursors, or temporary table operations) that continues to use tempdb while the shrink or file removal operation is running. Because tempdb is recreated on every SQL Server restart and heavily used by concurrent sessions, this contention is common in high-throughput OLTP or reporting environments.

Step-by-Step Resolution Guide

Step 1: Identify Active Sessions Using tempdb

  • Run the following query to check for active tasks consuming tempdb space:
SELECT session_id, request_id, SUM(internal_objects_alloc_page_count) AS internal_pages,
       SUM(user_objects_alloc_page_count) AS user_pages
FROM sys.dm_db_task_space_usage
GROUP BY session_id, request_id
ORDER BY internal_pages DESC;
  • Look for sessions with high internal_objects_alloc_page_count, indicating heavy worktable/work file usage.
  • Note the session_id values for further investigation with sp_who2 or sys.dm_exec_requests.

Step 2: Pause or Reschedule the Shrink/Remove Operation

  • Avoid running DBCC SHRINKFILE or ALTER DATABASE tempdb REMOVE FILE during peak hours.
  • Schedule the operation during a maintenance window when query activity is minimal.
  • If using SQL Server Agent, create a job step that checks sys.dm_db_task_space_usage for near-zero internal_pages before executing the shrink command.

Step 3: Retry the Shrink Operation with Isolation

  • Temporarily restrict new connections to reduce tempdb contention:
ALTER DATABASE tempdb SET RESTRICTED_USER WITH ROLLBACK IMMEDIATE;
  • Re-run the shrink or file removal command:
DBCC SHRINKFILE (tempdev, 500);
  • Revert access once complete:
ALTER DATABASE tempdb SET MULTI_USER;

Step 4: Restart the SQL Server Service (If Contention Persists)

  • On Windows Server 2025 / Windows 11, open Services.msc (or use PowerShell: Restart-Service MSSQLSERVER).
  • Restarting SQL Server recreates tempdb from scratch, clearing all IAM chains and worktable allocations.
  • After restart, immediately attempt the shrink or file removal operation before other processes begin consuming tempdb again.

Step 5: Adjust tempdb File Configuration (Long-Term Fix)

  • Instead of shrinking tempdb repeatedly, evaluate whether the file growth is due to legitimate workload needs (e.g., large sorts, version store usage, or temp table proliferation).
  • Use SQL Server Management Studio (SSMS) to navigate to Database Properties > Files for tempdb and set appropriate initial size and autogrowth values to reduce the need for frequent shrink operations.

Alternative or Verification Steps

  • Verify the fix by re-running the DBCC SHRINKFILE or file removal command and confirming it completes without Error 5054; also check sys.dm_db_file_space_usage to confirm space was reclaimed.
  • If the issue persists even during low-activity periods, check for orphaned or long-running transactions using DBCC OPENTRAN(tempdb) and consider failing over the instance (if in an Always On Availability Group) to force a clean tempdb rebuild.