Shrink TempDB Files

Modified on Fri, 11 Sep at 5:28 PM

If you cannot restart the SQL service, you can shrink the tempdb files:


USE tempdb;
GO

-- 0. List files and sizes
SELECT file_id, name, size/128 AS CurrentSizeMB 
FROM sys.database_files;

-- 1. Flush dirty pages to disk
CHECKPOINT;
GO

-- 2. Clear cached execution plans holding tempdb object allocations
DBCC FREEPROCCACHE;
GO

-- 3. Clear tempdb system/buffer caches
DBCC FREESYSTEMCACHE ('tempdb');
GO
Immediately retry shrinking each file:

SQL
USE tempdb;
GO
DBCC SHRINKFILE (tempdev, 1024);
DBCC SHRINKFILE (temp2, 1024);
DBCC SHRINKFILE (temp3, 1024);
DBCC SHRINKFILE (temp4, 1024);
DBCC SHRINKFILE (temp5, 1024);
DBCC SHRINKFILE (temp6, 1024);
DBCC SHRINKFILE (temp7, 1024);
DBCC SHRINKFILE (temp8, 1024);
DBCC SHRINKFILE (templog, 1024);
GO

Was this article helpful?

That’s Great!

Thank you for your feedback

Sorry! We couldn't be helpful

Thank you for your feedback

Let us know how can we improve this article!

Select at least one of the reasons
CAPTCHA verification is required.

Feedback sent

We appreciate your effort and will try to fix the article