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

Question: How to Shrink TempDB Without SQL Server Restart?

Answer: During recent On Demand (55 minutes) consulting engagement, I was tasked with this task. When the organization reached out to me, I asked what could be the reason they want me to be on call as Shrinking TempDB is a ubiquitous task and they do not need any help. However, they were pretty adamant that they do want my presence during the call. Well, customers are always right (well, I know you may have a different opinion about it), so I got on the call.

As soon as I got online, I realize what their issue was. They were not able to shrink their TempDB even though they were running Shrink command multiple times.

Before you all jump and start talking about I must not Shrink Database as that is bad for the system and performance, I agree. Please read the following blog posts written to support that concept.

• SQL SERVER – SHRINKDATABASE For Every Database in the SQL Server
• SQL SERVER – What the Business Says Is Not What the Business Wants
• SQL SERVER – Shrinking Database is Bad – Increases Fragmentation – Reduces Performance
• SQL SERVER – Database Worst Practices – New Town and New Job and New Disasters
• SQL SERVER – SHRINKFILE and TRUNCATE Log File in SQL Server
• SQL SERVER – Shrinking NDF and MDF Files – Readers’ Opinion

Now, let us see how we can shrink the TempDB database.

CHECKPOINT
GO
DBCC FREEPROCCACHE
GO
DBCC SHRINKFILE (TEMPDEV, 1024)
GO

When the users were running only Shrinkfile, they were not able to shrink the database. However, when they ran DROPCLEANBUFFERS it worked just fine.

However, please note that DROPCLEANBUFFERS will remove all the procedure cache, which may slow down some systems right after as they have to rebuild all the procedure cache again. Again, shrink your TempDB ONLY if you are running out of the space or in crucial situations. If you reach the point where you have to restart the services to shrink the database, you may consider running DBCC FREEPROCCACHE (as considerably when you restart the systems, you not only remove cache but also cached data pages).

Reference: Pinal Dave (https://blog.sqlauthority.com)