Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 

The Voice of the DBA

Steve Jones is the editor of SQLServerCentral.com and visits a wide variety of data related topics in his daily editorial. Steve has spent years working as a DBA and general purpose Windows administrator, primarily working with SQL Server since it was ported from Sybase in 1990. You can follow Steve on Twitter at twitter.com/way0utwest

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.


Filed under: Blog Tagged: sql server, syndicated

Comments

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)

Leave a Comment

Please register or log in to leave a comment.