I am not a proponent of shrinking databases, but sometimes you have to because your momma said to. Sometimes that database is tempdb.
It used to be that we were warned against shrinking tempdb because it could cause corruption, so your only recourse was to restart the SQL Server service. Paul Randal let us know that this is no longer a problem.
LET’S SAY YOU HAVE TO SHRINK TEMPDB
Like your life depended on it. Or perhaps you needed the alerts to stop.
If a user ran an atrocious adhoc query that caused your tempdb to grow so much that it caused your disk space alert to fire and:
- you needed that alert to stop
- the storage team is not going to give you more space
- the user promised to never do that again
So you try to shrink tempdb, but it just won’t shrink.
Try clearing the plan cache:
And then try shrinking tempdb again.
I came across this solution recently when I had to shrink tempdb. I tried shrinking each of the 8 data files plus CHECKPOINTs, repeatedly. It would not budge. I almost threw in the towel and emailed out that the space issue would be fixed during our next patching window, but then I found David Levy’s reply. DBCC FREEPROCCACHE worked like a charm.