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)
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
Posted - 02/24/2010 : 08:55:45
. But try that first. (Shrinking TEMPDB requires putting the server in single user mode ... that's going to require two restarts!)