No Shrink-y Tempdb

I caught the end of a Twitter conversation the other day between Wes Brown (Blog | @WesBrownSQL), Paul Randal (blog | @PaulRandal), and Aaron Bertrand (blog | @aaronbertrand) talking about shrinking tempdb and the potential issues.

I’ve never thought about it before, but someone investigated and the question popped up on Twitter. Nioholas Cain (blog | @SirSQL) wrote a nice blog post on the events, and it explains how he came to the question (a full disk).

The short answer is that you can cause corruption in tempdb if there is activity taking place when you shrink.

So don’t shrink tempdb!

At least not without quiescing SQL Server. If you don’t know what that means, don’t shrink tempdb on your active server. In case you don’t know, it means allowing active connections to drain off and not allowing new ones. There’s a KB article on this.

About way0utwest

Editor, SQLServerCentral
This entry was posted in Blog and tagged , . Bookmark the permalink.

1 Response to No Shrink-y Tempdb

  1. Tim Radney says:

    I have found that when tempdb has grown out of control due to some erroneous process or end user it is just as easy to cycle the service to allow it to recreate at its normal size. I have seen debates where others say to shrink etc but the cases I have been involved with the server was pretty much already toast due to zero drive space being available. The quickest and easiest response was the SQL Server instance.


Comments are closed.