Subscribe to this blog
Briefcase
Print
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.
Filed under: Blog Tagged: sql server, syndicated
Posted by Adam Mikolaj on 26 May 2011
After reading the MS page, method 3 reminded me of a SQLRAP session I had awhile ago where the MS guys really pushed splitting the tempdb up into as many processors as your machine has (ex - 4 processors, 4 tempdb files).
Wouldn't this help reduce giant tempdbs (aka no need for shrink-a-dink)?
Posted by Jason Brimhall on 26 May 2011
Good solid recommendation and more reason to not shrink - at least not without knowing the facts first.
Posted by Jon Russell on 27 May 2011
Adam,
While using multiple tempdb files will reduce possible allocation contention, I do not think it will reduce the overall space needed for the tempdb database. The same amount of space will just be spread over 4 files instead of one.
Glenn Berry had a good post on the subject: www.sqlservercentral.com/.../some-suggested-sql-server-2008-r2-instance-configuration-settings.aspx
Regards,
Jon
Posted by Jon Russell on 27 May 2011
Good post. I love posts like these that provide links to related topics and discussions.
Posted by h.tobisch on 30 May 2011
Is this for any good to shrink a tempdb as it is rebuilt automatically when restarting your server.
Posted by jay holovacs on 31 May 2011
Simplest approach, if feasible, is to find a time window to restart the sql server service. Down for a very short time and comes up clean. (at least in my experience)