Work Table Page Could Not Be Moved: DBCC SHRINKFILE on TempDB

A work table page can stop DBCC SHRINKFILE before tempdb reaches the size you asked for. In this 2017 client case, several tempdb files were large and unevenly sized. The same page kept blocking the shrink, even after a restart.

What the Message Tells You

DBCC SHRINKFILE: Page 1:26423878 could not be moved because it is a work table page.

The message says that shrink could not move that page. It does not say the file is corrupt. Repeating the command will not tell you whether the workload still needs the space.

Tempdb holds temporary tables, internal work such as sorts and spools, and the version store. A work table page is internal work space. Find out what the workload is doing before you choose a smaller file size.

Compare Configured Size With Current Usage

The first query compares the startup size recorded in master with the current tempdb file sizes. The second splits the space in the data files by type. Both only read.

USE tempdb;
GO
SELECT df.file_id, df.name, df.type_desc,
       mf.size / 128.0 AS ConfiguredSizeMB,
       df.size / 128.0 AS CurrentSizeMB
FROM sys.database_files AS df
JOIN master.sys.master_files AS mf
  ON mf.database_id = DB_ID(N'tempdb') AND mf.file_id = df.file_id
ORDER BY df.file_id;
SELECT file_id,
       unallocated_extent_page_count / 128.0 AS FreeExtentMB,
       user_object_reserved_page_count / 128.0 AS UserObjectMB,
       internal_object_reserved_page_count / 128.0 AS InternalObjectMB,
       version_store_reserved_page_count / 128.0 AS VersionStoreMB
FROM sys.dm_db_file_space_usage
ORDER BY file_id;

My instance has eight data files configured at 8 MB, and the log file is configured at 8 MB too. Before my tests every data file was at 8 MB. Afterwards each showed 72 MB, and the log had grown to 136 MB. That is why the two size columns can differ. The second query lists the data files only. After growth, free extents were near 70 MB per file, with almost no internal object space.

Free extents do not guarantee that shrink can reach your target. Internal object space does not name the query that owns a work table page. The DMV needs VIEW SERVER STATE on SQL Server 2019 and earlier. On SQL Server 2022 and later it needs VIEW SERVER PERFORMANCE STATE.

Find the Sessions That Hold the Space

To see the owner, I built a small test. The first block creates 200,000 rows in a test database and copies them into a temp table. The session then holds that space until the table goes away.

CREATE TABLE dbo.TeaOrders (OrderId int IDENTITY PRIMARY KEY, TeaName varchar(40) NOT NULL, Note varchar(400) NOT NULL);
INSERT dbo.TeaOrders (TeaName, Note)
SELECT CHOOSE(1 + n % 4, 'Assam', 'Sencha', 'Jasmine', 'Darjeeling'), REPLICATE('x', 300)
FROM (SELECT TOP (200000) ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) AS n
      FROM sys.all_objects AS a CROSS JOIN sys.all_objects AS b) AS x;
GO
SELECT OrderId, Note INTO #Held FROM dbo.TeaOrders;
GO

This query shows the user object and internal object pages that each user session holds. The counters update when a batch ends, so run it in a separate batch.

SELECT s.session_id, s.login_name, s.status,
       (u.user_objects_alloc_page_count - u.user_objects_dealloc_page_count) / 128.0 AS UserObjectsMB,
       (u.internal_objects_alloc_page_count - u.internal_objects_dealloc_page_count) / 128.0 AS InternalObjectsMB
FROM sys.dm_db_session_space_usage AS u
JOIN sys.dm_exec_sessions AS s ON s.session_id = u.session_id
WHERE s.is_user_process = 1
ORDER BY (u.user_objects_alloc_page_count - u.user_objects_dealloc_page_count)
       + (u.internal_objects_alloc_page_count - u.internal_objects_dealloc_page_count) DESC;

My session held 8,000 user object pages (62.5 MB). Drop the table to release the space.

DROP TABLE #Held;

In my test, the session counter still showed 62.5 MB five seconds after the drop. Read these counters as a pointer to the owner, not as a live meter. The file space query above shows the current state of the files.

Check Before You Shrink

1. Record the configured and current size of every tempdb file.
2. Find the sessions that hold space, and ask their owners whether the work is finished.
3. Check whether a long transaction or a large sort is still running.
4. Pick a target that fits the normal peak, not the quietest hour.
5. Plan any restart or maintenance window with the service owner.

If a session still holds the space, shrink has nothing to release. Ending that work, with its owner’s agreement, comes first.

Size for the Workload

If the workload needs the space again and again, keep an allocation that fits it. Choose data file sizes and growth increments together, with room for normal peaks. Equal-size data files with the same growth setting are a sound baseline. The transaction log has a different role.

If growth came from a one-time operation, confirm that the operation has finished and that the target is realistic. A planned reduction can be right. Routine shrink and grow cycles add work and fix nothing.

The Historical Maintenance Workaround

The original incident used downtime and a minimal configuration startup to reset the initial tempdb file sizes. The 500 MB size and growth values were example choices, not sizing advice for every server. Tempdb is recreated when SQL Server starts, but a restart does not remove the workload that caused the growth.

Treat minimal configuration startup as a planned maintenance step, not the first response to this message. Review the procedure for your version and the service dependencies first. I did not shrink tempdb or run that procedure on my test instance.

For more background, read why routine database shrinking is a poor maintenance habit.

A smaller tempdb file is not the goal, it is the right size for the workload.

Published by Pinal Dave on SQLAuthority. More of my work at pinaldave.com.