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 2008 Forums
 SQL Server Administration (2008)
 tempdb size
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

skashifz
Starting Member

Australia
3 Posts

Posted - 06/11/2014 :  01:06:32  Show Profile  Reply with Quote
Hello,

My tempdb size grew 100+ gb. I restarted the server, but the size is still same even after restarting and is not coming down. Any one can give any idea what I might be missing. I thought size will come back to normal. I am talking about the .mdf file here.

Kind Regards
Syed

jackv
Flowing Fount of Yak Knowledge

United Kingdom
2079 Posts

Posted - 06/11/2014 :  01:29:21  Show Profile  Visit jackv's Homepage  Reply with Quote
Check the initial size setting - what is the current value?
If the "initial size" is small, Are there any jobs running when you start up - such as reindexing - which are causing the tempdb to expand?

Jack Vamvas
--------------------
http://www.sqlserver-dba.com
Go to Top of Page

skashifz
Starting Member

Australia
3 Posts

Posted - 06/11/2014 :  02:03:21  Show Profile  Reply with Quote
When I look at the initial size of tempdb, it appears it has changed and one file is 85GB. There are a total of 8 and when I initially created, they were same size. Now all are different 7 files approximately same size (around 3G) but 1 has grown to 85GB plus. Whats the best size for the file ?
Go to Top of Page

James K
Flowing Fount of Yak Knowledge

3760 Posts

Posted - 06/11/2014 :  11:29:04  Show Profile  Reply with Quote
quote:
Originally posted by skashifz

When I look at the initial size of tempdb, it appears it has changed and one file is 85GB. There are a total of 8 and when I initially created, they were same size. Now all are different 7 files approximately same size (around 3G) but 1 has grown to 85GB plus. Whats the best size for the file ?

I did not quite understand what you stated. Usually there is one data file and one log file for the tempdb by default. You can (and people often do have) more than one data file for the tempdb. You can have more than one log file as well, but there is no particular benefit to having more than one log file.

So, are you saying that you have multiple data files for the tempdb? If that is the case, then when you restart the server, all those files should get reset to their initial specified size (look in the files tab of the properties dialog that you see when you right-click on tempdb in object explorer and select properties).

After you restart, if the files are growing, that means there is some job that requires lot of space. A common culprit is rebuilding large indexes.

The best size for the tempdb depends on your needs. A general rule is allocate as much space as you think you will need, so the file will never have to autogrow. But do leave the autogrowth option enabled.
http://technet.microsoft.com/en-us/library/ms175527(v=sql.105).aspx
Go to Top of Page

jackv
Flowing Fount of Yak Knowledge

United Kingdom
2079 Posts

Posted - 06/12/2014 :  01:37:01  Show Profile  Visit jackv's Homepage  Reply with Quote
Another rule of thumb is to set the tempdb to the size of the largest index . Some other tactics for performance can be found on : http://www.sqlserver-dba.com/2011/04/tempdb-performance-and-strategy-checklist.html

Jack Vamvas
--------------------
http://www.sqlserver-dba.com
Go to Top of Page

skashifz
Starting Member

Australia
3 Posts

Posted - 06/13/2014 :  01:14:31  Show Profile  Reply with Quote
Hey guys, Thank you very much. After the restart, tempdb still had 85GB as the initial size. In properties window, I changed the size of all the data files of tempdb and brought them down to 1GB. and it release all the unwanted space by itself without restarting and the space used became around 3GB. Main thing is that the space was released.

Thanks very much for your help.
Go to Top of Page

jackv
Flowing Fount of Yak Knowledge

United Kingdom
2079 Posts

Posted - 06/16/2014 :  01:39:16  Show Profile  Visit jackv's Homepage  Reply with Quote
Your welcome,

Jack Vamvas
--------------------
http://www.sqlserver-dba.com
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