SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2005 Forums
 SQL Server Administration (2005)
 Temp Diagnosis
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

pradeep_iete
Yak Posting Veteran

India
84 Posts

Posted - 02/24/2010 :  08:55:45  Show Profile  Reply with Quote
Hi,

I have Production database.
SIze : 125 GB
TempDB : 900 MB

Now i want to reduce Temp DB size.Is it worth Reducing it and what is safe way to have size reduction.

Thankx.

Edited by - pradeep_iete on 02/24/2010 08:56:05

sodeep
Flowing Fount of Yak Knowledge

USA
7174 Posts

Posted - 02/24/2010 :  10:17:21  Show Profile  Reply with Quote
Did you check other posts? There are lots of posts recently regarding this.
Go to Top of Page

Kristen
Test

United Kingdom
22415 Posts

Posted - 02/24/2010 :  11:41:17  Show Profile  Reply with Quote
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)
Go to Top of Page

pradeep_iete
Yak Posting Veteran

India
84 Posts

Posted - 02/25/2010 :  00:37:56  Show Profile  Reply with Quote
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
Go to Top of Page

Kristen
Test

United Kingdom
22415 Posts

Posted - 02/25/2010 :  02:08:16  Show Profile  Reply with Quote
"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.
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.08 seconds. Powered By: Snitz Forums 2000