Shrink TempDB - by Vittorio Pavesi

Status : 

  Won't Fix<br /><br />
		Due to several factors the product team decided to focus its efforts on other items.<br /><br />
		A more detailed explanation for the resolution of this particular item may have been provided in the comments section.

Sign in
to vote
ID 496872 Comments
Status Resolved Workarounds
Type Suggestion Repros 0
Opened 10/9/2009 6:35:00 AM
Access Restriction Public


Allow to shrink tempdb without restarting SQL Server
Sign in to post a comment.
Posted by Ryan [MSFT] on 2/6/2012 at 11:10 AM
Thank you for submitting this suggestion, but given its priority relative to the many other items in our queue, it is unlikely that we will actually complete it. As such, we are closing this suggestion as “won’t fix”. If you feel that this is worth reconsidering, feel free to respond to this message and we will take another look.

Ryan Stonecipher
Senior Program Manager
Posted by Quickdraw on 11/7/2010 at 2:52 PM
Really Emily? You can shrink tempdb?

I need to kill some worktables used by internal SQL Server processes (index rebuilds I suspect but can't prove). The objects are scattered inside a 23gb tempdb and I can't reorganize the tempdb data file, so I can't shrink it:

DBCC SHRINKFILE: Page 1:2933472 could not be moved because it is a work table page.
DbId FileId     CurrentSize MinimumSize UsedPages EstimatedPages
------ ----------- ----------- ----------- ----------- --------------
2     1         2933480     1024        280         280

I can't figure out a way of cleaning this up without restarting the SQL Server.

Posted by ChrisRDBA on 6/10/2010 at 7:28 AM
This is a great idea!!!

A couple months back I had a runaway process that grew the tempDB by 1 GB per minute (stopped around 25 GB). This was due to poorly written code, but it still required a reboot to resolve. See post below for a complete description.

Posted by SQLWork on 6/10/2010 at 6:40 AM
I agree with Vittorio that this can be a problem if something ran amuck and tempdb got overly large on an array that is shared with other databases. Although good database size management would mitigate the impact, it does not make it go away completely.

When tempdb has its own array the limit is the array size, so there is little need to shrink.

Therefore, when tempdb is on a shared array, limiting its growth to a preset size would provide some protection to the overall array space. Of course, if wrong sized it would cause other problems of transaction failure.
Posted by Vittorio Pavesi on 11/18/2009 at 3:01 AM
I know you can shrink in 3 ways as described in kb 307487 but method 1 and 2 require restart and method 3 require tempdb to NOT BE IN USE.

Posted by Microsoft on 11/17/2009 at 2:53 PM
Hi Vittorio,
We do actually allow shrink of tempDB today. Can you elaborate on what you're doing, and what the issue is that you're seeing?