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.

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.

How to Shrink TempDB Without SQL Server Restart? - Interview Question of the Week #163 shrinkdatabase-800x211

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)

, , ,
Previous Post
How to Reduce High Virtual Log File (VLF) Count? – Interview Question of the Week #162
Next Post
What is Alternative to CASE Statement in SQL Server? – IIF Function – Interview Question of the Week #164

Related Posts

4 Comments. Leave new

Leave a Reply

Menu