SQL SERVER – DBCC SHRINKFILE: Page 1:26423878 Could not be Moved Because it is a Work Table Page – TempDB Files

SQL SERVER - DBCC SHRINKFILE: Page 1:26423878 Could not be Moved Because it is a Work Table Page - TempDB Files tempdb This blog is an outcome of an email communication with my client. The issue was that they were having big tempdb files. They had multiple files and of uneven size. As soon as they restart SQL, the initial size itself was huge and when they tried to shrink the TempDB database, it was not doing anything. Here was the message in SQL Server Management Studio query window.


DBCC SHRINKFILE: Page 1: 26423878 could not be moved because it is a work table page.
DBCC execution completed. If DBCC printed error messages, contact your system administrator.

If we try to covert page number to location in MDF file, it was 26423878 * 8 KB = ~201 GB and that was the size on which they were stuck. We were seeing same page every time we try to shrink, even after restart of SQL Server. What was the option now?


Please note that you try everything out on your development server before experimenting on the production server.

We need to restart SQL with a special switch -f which is used to start SQL with minimal configuration. Note: this needs downtime of SQL Server for some time as no one should connect to SQL.

  • Stop SQL Server service.
  • Start SQL Server in minimal mode from command prompt.
  • Above command starts SQL Server in minimal configuration (f switch) and allows connections from the application named SQLCMD (mSQLCMD)
  • Connect to SQL using SQLCMD
  • In the SQLCMD connection, execute the following command for each one of the tempdb data files to set their initial size to 500 MB and file growth also to 500 MB increments.
  • Repeat this process for every tempdb data file.
  • Now verify the initial size has taken effect using the following query.
SELECT name ,size*8.0/1024  'Initial Size in MB'
FROM master.sys.sysaltfiles WHERE dbid = 2
  • Once size has come down to expected size, we need to stop SQL service NET STOP MSSQLSERVER
  • And now, start again normally.
  • Relax as tempdb size has been reduced.

Note: MSSQLSERVER in the above command is for the default instance. So, if you have named instance, it would be MSSQL$InstanceName

Have you ever seen such error during shrink? Did you find another way?

Please note that Shrinking Database is not recommended process and it can lead to slowest possible performance. You must read this article Shrinking Database is Bad – Increases Fragmentation – Reduces Performance.

Reference: Pinal Dave (http://blog.SQLAuthority.com)

SQL Scripts, SQL Server, SQL Server DBCC, SQL Server Management Studio, SQL TempDB
Previous Post
SQL SERVER – Microsoft.SqlServer.Management.Sdk. Sfc.EnumeratorException: Failed to Retrieve Data for This Request
Next Post
SQL SERVER – AlwaysOn Listener Error – The WSFC Cluster Could Not Bring the Network Name Resource With DNS Name ‘DNS name’ Online

Related Posts

3 Comments. Leave new

  • Hello,


    dbcc shrinkfile (tempdev, 5000)

    did it without restart

  • Devesh Srivastava
    April 3, 2019 7:19 pm

    Yes, it always works with the steps above, without restarting it.

  • Jermaine_DBA_Genie
    August 30, 2021 8:46 pm

    It does not shrink in High Availability that easily with free procedure cache and session cache. I even do checkpoint and drop clean buffers with shrink file. Usually does not move in HA. But I will try the blog post recommendations and report back


Leave a Reply