Sql-server – Need to move TempDB to another drive. But its files cannot be dropped or shrunk

shrinksql servertempdb

Today I had a task to move all TempDB files to a new, smaller drive (RAMdisk).
I analysed TempDB
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.

Best Answer

It turns out there are a couple of ways of quickly shrinking or emptying TempDB files and drop them. The first way.

  1. Change locations and sizes for all TempDB data and log files in system catalogs and restart SQL instance.
    • ALTER DATABASE tempdb MODIFY FILE(NAME = tempdev, FILENAME = '<file_location>', SIZE = <size>);
    • ALTER DATABASE tempdb MODIFY FILE(NAME = templog, FILENAME = '<file_location>', SIZE = <size>);
  2. Restart SQL Server instance, drop old TempDB files

And one more way.

  1. Start SQL Server with minimal configuration - using -f parameter and connect via sqlcmd

  2. Delete unneeded files:

    ALTER DATABASE tempdb REMOVE FILE [<LogicalFileName>]
Related Question