Removing Tempdb .ndfs

Question

Tempdb is configured with 8 files and we are reducing them to 4. I read many blogs where SQL Server will allow you to remove excess .ndf's if you run the 4 dbcc drop and free statements, then run the dbcc shrinkfile with the emptyfile clause, then the alter db command with the remove file clause. When this was attempted, the error

DBCC SHRINKFILE: Page 9:364016 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 "tempdev8" to other places to complete the emptyfile operation. DBCC execution completed. If DBCC printed error messages, contact your system administrator.

My question:

Should I attempt to remove the locked temp tables by identifying them, then, re-run the dbcc statements, or, simply run an alter statement, then restart the instance, or restart the instance, then rerun the alter? I realize the issue that the temp tables are being used by tempdb consistently which is causing the remove file not to work.

Answer 1

Per Mike Rose's answer on the Microsoft Social site, I believe this should work:

DBCC FREESYSTEMCACHE ('ALL');

DBCC FREEPROCCACHE;

There will be some performance hit for this as SQL Server will recreate Query Plans, but it should allow you to shrink TEMPDB.

Answer 2

I first run the following to see if I can remove the file without having to restart the sql service.

USE [tempdb]
GO
DBCC DROPCLEANBUFFERS
GO
DBCC FREEPROCCACHE
GO
DBCC FREESESSIONCACHE
GO
DBCC FREESYSTEMCACHE ( 'ALL')
GO
DBCC SHRINKFILE (N'temp5' , EMPTYFILE)
GO
ALTER DATABASE [tempdb] REMOVE FILE [temp5]
GO

If the files are blocked, then I alter the size and file growth. Then wait for the next service window were the sql service can be restarted. Then the files are small, empty, and locked for growth. Then you can easily delete them.

--before the sql server restart
USE [master]
ALTER DATABASE TempDB MODIFY FILE
(NAME = temp5, SIZE = 1KB, FILEGROWTH = 0 )
GO

--then after the sql server restart
USE [TempDB]
DBCC SHRINKFILE (N'temp5' , EMPTYFILE)
GO
ALTER DATABASE [tempdb] REMOVE FILE [temp5]
GO