SQL Server Home
Thank you for your feedback!
- by Vittorio Pavesi
as Won't Fix
Due to several factors the product team decided to focus its efforts on other items.
A more detailed explanation for the resolution of this particular item may have been provided in the comments section.
10/9/2009 6:35:00 AM
Allow to shrink tempdb without restarting SQL Server
ATTACH A FILE
EDIT THIS ITEM
Item can only be reassigned when it is active.
to post a comment.
Please enter a comment.
[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.
Senior Program Manager
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.
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.
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.
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.
on 11/17/2009 at 2:53 PM
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?
to post a workaround.
Please enter a workaround.
Attach a file
© 2016 Microsoft