How to Shrink TempDB Without SQL Server Restart? – Interview Question of the Week #163

Question: Can you shrink tempdb without restarting SQL Server?

Answer: Sometimes. DBCC SHRINKFILE can reclaim space while SQL Server is online, but it may stop above the target when pages can’t be moved or released. A customer once booked an on-demand consulting call because repeated DBCC SHRINKFILE attempts were not making tempdb smaller. I asked why they needed me on the call for what sounded like a routine command. They insisted, and once I joined I understood: the command was running, but the space they hoped to reclaim was still in use.

So yes, tempdb can sometimes be shrunk while SQL Server stays online. But don’t start by clearing caches or repeatedly issuing shrink. First find which file grew, how much is used, and whether the workload is likely to need the space again. tempdb is recreated on service start at its configured size; a large current size alone is not proof of a performance problem.

USE tempdb;
SELECT name AS LogicalFileName,
       type_desc AS FileType,
       size / 128.0 AS AllocatedMB,
       FILEPROPERTY(name, 'SpaceUsed') / 128.0 AS UsedMB
FROM sys.database_files
WHERE type_desc = N'ROWS'
ORDER BY file_id;

If there is a genuine one-time space emergency, identify the affected logical file and choose a target that still supports normal workload. In the example below, tempdev must actually be the logical file name, and 1,024 MB must be an appropriate target in your environment.

-- Example only, after measuring usage and identifying this file.
USE tempdb;
DBCC SHRINKFILE (N'tempdev', 1024);

A shrink may stop above the target when pages remain in use or concurrent tempdb activity gets in the way. Investigate the workload and repeat the measurement, not the command in a loop. A quiet period improves the chance that the shrink reaches its target.

Clearing Caches Is Not a Shrink Step

You’ll often see DBCC FREEPROCCACHE or DBCC DROPCLEANBUFFERS suggested next to a tempdb shrink. They do different things. FREEPROCCACHE clears cached plans; DROPCLEANBUFFERS removes clean data pages from the buffer pool. Neither belongs in a shrink recipe. Clearing the plan cache can trigger widespread recompilation, so it’s not a harmless alternative to a restart.

I would rather fix the cause of unexpected growth than celebrate a smaller number for one afternoon.

A big tempdb is not the problem, it is the symptom, so measure what filled it before you shrink it.

Published by Pinal Dave on SQLAuthority. More of my work at pinaldave.com.