Tempdb won’t shrink

Tempdb database is one of the most important databases among the system databases for Microsoft SQL Server. However, this is also the most used and abused database, sometimes known as and abused as a public toilet as well. So, the thing is, the TEMPDB is most frequently used database. And, that is why DBAs prefer to have it on a separate and fast disks so it can perform well. Another important part is that it need to have ample space to grow.

About a month ago, an aspiring DBA whom I am mentoring has called me. He was having trouble reclaiming space back from the tempdb. The server is a highly critical production server; well almost all the production servers are highly business critical servers for that matter. So, the conversion between him and I went like this:

Aspiring DBA: Hi Hemantgiri! How are you doing?

I : Hi, I am doing good. How are you and family doing.

Aspiring DBA: We are doing good. And, this time, I need your advice.

I: Sure, how can I help?

Aspiring DBA: I have this production server. Tempdb is housed on it’s own disk and currently tempdb is outgrown. I can’t shrink it.

I: Did you tried find out what are the sessions running and holding tempdb?

Aspiring DBA: I tried and find a few. But things are not helping.

I: Let me share some commands with you, run them and let me know if that works for you. But, please beware some of these commands can cause performance decrease for a brief time. However, it will help you reclaim the space. And, ultimately will save you from restarting the SQL Server service or server restart.

Caution: Running the below commands will clear all the cached index and data pages, and clears all the procedure cache. This will help you reclaim the space from tempdb back to the disk at the cost of cached execution plans. These plans will get rebuilt the next time the procedure runs. And, do remember to take explicit permission from the application owner or business before running these commands in production system.

use tempdb
go
sp_helpfile
go
sp_spaceused
go
CHECKPOINT
go
dbcc freeproccache
go
dbcc dropcleanbuffers
go
dbcc shrinkfile(tempdev, 2500)
go
dbcc shrinkfile(temp2, 2500)
go
dbcc shrinkfile(temp3, 2500)
go
dbcc shrinkfile(temp4, 2500)
go
dbcc shrinkfile(temp5, 2500)
go
dbcc shrinkfile(temp6, 2500)
go
dbcc shrinkfile(temp7, 2500)
go
dbcc shrinkfile(temp8, 2500)
go
dbcc shrinkfile(templog, 2500)
go
sp_spaceused
go

This commands did help the aspiring DBA to reclaim the space from tempdb successfully. However, thing to notice here is that these commands can cause a brief or large performance issue but its useful in cases when you can not restart the server or the service. I would like to quote explanation from the Microsoft site.

Explaining the commands:

CHECKPOINT

A checkpoint creates a known good point from which the SQL Server Database Engine can start applying changes contained in the log during recovery after an unexpected shutdown or crash.

https://docs.microsoft.com/en-us/sql/relational-databases/logs/database-checkpoints-sql-server?view=sql-server-ver15

DBCC FREEPROCCACHE

Removes all elements from the plan cache, removes a specific plan from the plan cache by specifying a plan handle or SQL handle, or removes all cache entries associated with a specified resource pool.

https://docs.microsoft.com/en-us/sql/t-sql/database-console-commands/dbcc-freeproccache-transact-sql?view=sql-server-ver15

DBCC DROPCLEANBUFFERS

Removes all clean buffers from the buffer pool, and columnstore objects from the columnstore object pool.

https://docs.microsoft.com/en-us/sql/t-sql/database-console-commands/dbcc-dropcleanbuffers-transact-sql?view=sql-server-ver15