SQL Server 2014. Suddenly we can’t seem to remove extra tempdb files (which were added after we reduced CPU).
I’m getting this error:
DBCC SHRINKFILE: Page 9:505072 could not be moved because it is a work table page. Msg 2555, Level 16, State 1, Line 94 Cannot move all contents of file "tempdev8" to other places to complete the emptyfile operation. DBCC execution completed. If DBCC printed error messages, contact your system administrator. Msg 5042, Level 16, State 1, Line 95 The file 'tempdev8' cannot be removed because it is not empty.
Since it was a test server I could try to bounce the server. But even on restart it still occurred. The only fix was to put SQL into single user mode and run the following.
USE [tempdb]
GO
CHECKPOINT;
GO
DBCC DROPCLEANBUFFERS;
GO
DBCC FREEPROCCACHE
GO
DBCC FREESYSTEMCACHE ('ALL');
GO
DBCC FREESESSIONCACHE;
GO
DBCC SHRINKFILE ('tempdev8' , emptyfile)
GO
ALTER DATABASE [tempdb] REMOVE FILE [tempdev8]
GO