Remember when we thought that in order to shrink Tempdb we had to put the server into single user mode to prevent data corruption ? Yes thats correct, – past tense !!! We don’t have to to do this as suggested by Paul Randall in this post.
To Shrink or Not to Shrink…..? That is the question.
Lets say that somebody has run a really horrific query that has caused large growth in your tempdb datafile and you know after speaking with the user concerned that they just wouldn’t dream of running it again (at least while your the dba !), you can’t get more space for a while on your tempdb drive from your san guy, and you feel your only option is to shrink tempdb down to its’ normal size. Now normally I would say if something has grown to that size it’s for a reason but in certain spots there may be a case for shrinking back down.
The File Just Won’t Shrink….
So you try and shrink the file using either Tsql code or SSMS management studio but the file just won’t shrink.
Solution.
Run dbcc freeproccache and then try again. The file should shrink down to the size you set it to.
Don’t panic about running this command. There is a lot of what I would consider over cautious dba’s who fear that after running this command everything will start to run slower. There is truth in this, but only whilst sql server first complies the execution plan – you may see some cpu spikes but things will get back to normal pretty quickly. I’ve never run this command and had a major issue as regards performance.
If you are scared of running this then check out this blog post by Glen Berry who shows you how to use a more refined approach rather than the sledge hammer approach I am talking about, although to be honest, his methods may not work in relation to this article which is about why Tempdb won’t shrink – if you run dbcc freeproccache tempdb should shrink down.
sqlnights.com 