SQL SERVER – Msg 2555: Cannot Move All Contents of File to Other Places to Complete the Emptyfile Operation

Once upon a time, I was trying to remove TempDB database additional files, but it was not working. In this blog I would explain the steps needed fix Msg 2555: Cannot move all contents of the file to other places to complete the emptyfile operation.

THE SITUATION

My client installed SQL Server using an automated method which kept tempdb database files on C Drive. As a good practice, he decided to add new files to T drive and remove files from C drive. While removing the files from C drive, they encountered below error.

Msg 2555, Level 16, State 1, Line 18
Cannot move all contents of file “tempdata1” to other places to complete the emptyfile operation.

The command which they were running was below.

USE [tempdb]
GO
DBCC SHRINKFILE (N'tempdb1', EMPTYFILE)
GO
ALTER DATABASE [tempdb] REMOVE FILE [tempdb1]
GO

When I double checked the messages window and I also found below.

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

Above is the main cause why SQL was not able to empty the file and remove it from TempDB database.

WORKAROUND/SOLUTION

Based on various other blogs on the internet, below T-SQL queries were tried and they DID NOT help.

DBCC DROPCLEANBUFFERS;
GO
DBCC FREESYSTEMCACHE ('ALL');
GO
DBCC FREEPROCCACHE;
GO
DBCC FREESESSIONCACHE;
GO

Note: Even restart of SQL Service was not helping us here and we were getting the same message for page 3:39

I remember writing a blog about the work table related error message for the tempdb database.

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

In short, starting SQL with a minimal configuration using “f” startup parameter allowed us to get rid of extra files. Have you faced such a situation? What solution worked for you?

Reference: Pinal Dave (https://blog.sqlauthority.com)