1

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.

2

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>]
    
  • 1
    +2 on the first method, -1 on the second method :-) – Aaron Bertrand Aug 16 at 14:55
1

General algorithm:

  1. Determine the logical file names of the tempdb database and their current location on the disk:

    SELECT name, physical_name AS CurrentLocation
    FROM sys.master_files
    WHERE database_id = DB_ID(N'tempdb');

  2. Change the location of each file by using ALTER DATABASE:

    USE master;
    GO
    ALTER DATABASE tempdb
    MODIFY FILE (NAME = tempdev, FILENAME = 'E:\SQLData\tempdb.mdf');
    GO
    ALTER DATABASE tempdb
    MODIFY FILE (NAME = templog, FILENAME = 'F:\SQLLog\templog.ldf');
    GO

  3. Stop and restart the instance of SQL Server

  4. Verify the file change:

    SELECT name, physical_name AS CurrentLocation, state_desc
    FROM sys.master_files
    WHERE database_id = DB_ID(N'tempdb');

  5. Delete the tempdb.mdf and templog.ldf files from the original location

For more information, see here.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.