I am trying to remove extra tempdb data files in TEST instance. Steps that I am taking.
1. Restart SQL Server (It's test, doesn't matter if I restart it)
2. There are no users on the instance, then I use the below commands.
USE [tempdb] GO DBCC SHRINKFILE (N'tempdev1' , EMPTYFILE) GO
USE [tempdb] GO DBCC SHRINKFILE (N'tempdev2' , EMPTYFILE) GO
USE [tempdb] GO DBCC SHRINKFILE (N'tempdev3' , EMPTYFILE) GO
I get below error. DBCC SHRINKFILE: Page 3:24 could not be moved because it is a work table page. Msg 2555, Level 16, State 1, Line 7 Cannot move all contents of file "tempdev1" to other places to complete the emptyfile operation. DBCC execution completed. If DBCC printed error messages, contact your system administrator.
DBCC SHRINKFILE: Page 4:31 could not be moved because it is a work table page. Msg 2555, Level 16, State 1, Line 13 Cannot move all contents of file "tempdev2" to other places to complete the emptyfile operation. DBCC execution completed. If DBCC printed error messages, contact your system administrator.
DBCC SHRINKFILE: Page 5:20 could not be moved because it is a work table page. Msg 2555, Level 16, State 1, Line 19 Cannot move all contents of file "tempdev3" to other places to complete the emptyfile operation. DBCC execution completed. If DBCC printed error messages, contact your system administrator. 3.
I tried multiple times, then I ran following command which helped in emptying the files.
DBCC FREESYSTEMCACHE ('ALL')
DBCC FREEPROCCACHE
USE [tempdb] GO DBCC SHRINKFILE (N'tempdev1' , EMPTYFILE) GO
USE [tempdb] GO DBCC SHRINKFILE (N'tempdev2' , EMPTYFILE) GO
USE [tempdb] GO DBCC SHRINKFILE (N'tempdev3' , EMPTYFILE) GO
When you restart SQL Server doesn't it do the same thing that DBCC FREESYSTEMCACHE ('ALL') and DBCC FREEPROCCACHE does?
