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 |
|
pradeep_iete
Yak Posting Veteran
84 Posts |
 Posted - 2010-02-24 : 08:55:45
|
| Hi, I have Production database.SIze : 125 GBTempDB : 900 MBNow i want to reduce Temp DB size.Is it worth Reducing it and what is safe way to have size reduction.Thankx. |
|
|
sodeep
Master Smack Fu Yak Hacker
7174 Posts |
Posted - 2010-02-24 : 10:17:21
|
| Did you check other posts? There are lots of posts recently regarding this. |
 |
|
|
Kristen
Test
22859 Posts |
Posted - 2010-02-24 : 11:41:17
|
| Restart SQL Server, that should cause it to shrink to its "initial size".Do not attempt to Shrink TEMPDB - high risk of corrupting your databases unless you use the special, and complicated, mechanism (which is just not worth it assuming that restart does shrink it, as it is supposed to) |
|
|
|
pradeep_iete
Yak Posting Veteran
84 Posts |
Posted - 2010-02-25 : 00:37:56
|
| Is Restart is Safe bet?Also is it worth Resetting intitial size other then default setting considereing the size that temp has already taken i.e 900GB ?Thanks |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2010-02-25 : 02:08:16
|
"Is Restart is Safe bet?"It is supposed to work. Doesn't always  . But try that first. (Shrinking TEMPDB requires putting the server in single user mode ... that's going to require two restarts!)"Also is it worth Resetting intitial size other then default setting considereing the size that temp has already taken i.e 900GB ?"Definitely. However, I guess at this stage you don't know what is a reasonable size for your system ... if TEMPDB is 900MB (you didn't mean GB, right?!!) then maybe that was due to some exceptional process. If you set it back small, and watch the size over a couple of days you will get a feel for what is a reasonable initial size.Having SQL Server pre-allocate a reasonable size is much better than a small size and then SQL has to keep extending it lots of time to get to "normal" size. |
|
|
|
|
|
|
Temp Diagnosis