Problem:Temp database on SQL has grown to full size of the disk. Usually because of a bug/error in a scheduled task. There is no need to increase the disk size or replace with another disk. However other files (temp db log, …) are not able to perform well when the disk where they reside is full.

Shrinking our temp db is the only solution. Best practices tell us to always do this with a restart of the server. Sometimes, this just isn’t an option.

Solution:

Got this script from a colleague.

Caution: This will clear a lot of buffers. Temporary performance impact is to be expected.

DBCC FREEPROCCACHE
GO
DBCC DROPCLEANBUFFERS
go
DBCC FREESYSTEMCACHE (‘ALL’)
GO
DBCC FREESESSIONCACHE
GO
DBCC SHRINKFILE (TEMPDEV2,10240)
GO
DBCC SHRINKFILE (TEMPDEV,10240)
GO
DBCC SHRINKFILE (TEMPDEV3,10240)
GO
DBCC SHRINKFILE (TEMPDEV4,10240)
GO