Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
Author |
Topic |
cjp
Yak Posting Veteran
69 Posts |
Posted - 2010-07-12 : 11:19:38
|
I am using SQLS 2005 SP 2.On a computer that is now re-purposed to hold archives of old (medical) studies, I want to reduce the starting size of the temp db so as to reclaim the disk space that it is using. It is currently set to 125 GB, and I would like to re-set it to (say) 10 GB since I will no longer need a large amount of contiguous space in the temp db. I have tried various methods (including Alter Database, as advised by various obscure articles in BOL) but it doesn't work - I keep being told that I cannot alter the database because the new size is smaller than the current size.Is there a way to do this?Thanks.Chris |
|
tkizer
Almighty SQL Goddess
38200 Posts |
|
cjp
Yak Posting Veteran
69 Posts |
Posted - 2010-07-14 : 06:18:57
|
Thank you for the link, which I have read with interest. While researching the approaches raised in the link, I came across a (forum) solution that I cannot find in formal documentation, but it appears to work:dbcc shrinkfile ('tempdev', 100, TRUNCATEONLY)The person who suggested this fix adds that the TRUNCATEONLY instruction returns the released disk space to the operating system.Both I and a colleague have independently tested this and it does appear to reset the temp db to the value in MB specified in the expression. Can anyone comment on this solution? Is it likely to be safe and reliable?Thanks.Chris |
 |
|
Kristen
Test
22859 Posts |
Posted - 2010-07-14 : 06:50:19
|
DBCC SHRINKFILE is in the SQL DOCs so I don't foresee a problem. (Note that the SIZE ["100" in your example] will be ignored when using TRUNCATEONLY - SQL will just throw away anything that is unused at the end of the file - so, if there IS something used, you might not actually get right down to "100"]HOWEVER everything I have read says that it is only safe to shrink TEMPDB when it is not in active use, otherwise corruption of databases could occur. Recommendations generally suggest that SQL Service should be started in Manual / Single User mode in order to achieve this. Even if all your users are tucked up in their beds yous till run the risk that a scheduled task, or some remote application, connects and does something that would make use of TEMPDB |
 |
|
cjp
Yak Posting Veteran
69 Posts |
Posted - 2010-07-14 : 07:38:23
|
Thanks for this. - When I said that I couldn't find documentation of the method, I was referring to the use of shrinkfile on the starting size of tempdev (sorry not to have been clearer). My installations of SQL Server are always single-user and standalone (ie not on a network). For this reason, I can guarantee that tempdb is empty - that is, I simply started SQL Server (closed down overnight) and then, before doing anything else, ran shrinkfile on tempdev.From what you say, I think that shrinkfile will be safe if SQLS is switched off and then re-started before hacking the size of the temp db. This will only be done on machines that will no longer be used for heavyweight querying - very much storage rooms for old data that we cannot yet discard.By the way, option 1 in the Microsoft article derived from the above link (http://support.microsoft.com/kb/307487) fails for us for the very reason that we are single users; that is, having run the sqlservr -c -f process at the command prompt, we can't get into the database to hack tempdev...so the shrinkfile solution is an ideal answer.Thanks too for the note about the real effect of TRUNCATEONLY.Chris |
 |
|
Kristen
Test
22859 Posts |
Posted - 2010-07-14 : 11:35:22
|
"I think that shrinkfile will be safe if SQLS is switched off and then re-started before hacking the size of the temp db"Provided nothing else, including scheduled tasks, connects .... hence starting SQL Service in "single user mode" (or perhaps its called "emergency mode", I forget) is safer still. |
 |
|
|
|
|
|
|