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

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 (1,1024)
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?

WORKAROUND/SOLUTION

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.

NET START MSSQLSERVER /f /mSQLCMD

• 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.

ALTER DATABASE [tempdb] MODIFY FILE (NAME = N'tempdev',SIZE=500MB, FILEGROWTH = 500MB )
GO

• 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)