Today I had a task to move all
TempDB files to a new, smaller drive (
files space usage - not all the space inside of its files was used so I decided that new drive is ok.
So I had to shrink all the 8 files to make them small enough. I tried to do it with:
USE [tempdb] GO DBCC SHRINKFILE (N'<logicalname>' , <size>) GO
But it took a lot of time and eventually it didn't/almost didn't shrink files.
If I tried to empty a file :
USE [tempdb]; GO DBCC SHRINKFILE (N'<logicalname>', EMPTYFILE); GO
it still failed with a message:
DBCC SHRINKFILE: Page <pageNo> could not be moved because it is a work table page.
Then I found some posts about clearing the procedure cache, and the session cache:
DBCC DROPCLEANBUFFERS GO DBCC FREEPROCCACHE GO DBCC FREESESSIONCACHE GO DBCC FREESYSTEMCACHE ( 'ALL') GO
and I cleared everything, but no luck.