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.

 All Forums
 SQL Server 2008 Forums
 SQL Server Administration (2008)
 tempdb size

Author  Topic 

skashifz
Starting Member

3 Posts

Posted - 2014-06-11 : 01:06:32
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
Master Smack Fu Yak Hacker

2179 Posts

Posted - 2014-06-11 : 01:29:21
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

3 Posts

Posted - 2014-06-11 : 02:03:21
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
Master Smack Fu Yak Hacker

3873 Posts

Posted - 2014-06-11 : 11:29:04
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
Master Smack Fu Yak Hacker

2179 Posts

Posted - 2014-06-12 : 01:37:01
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

3 Posts

Posted - 2014-06-13 : 01:14:31
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
Master Smack Fu Yak Hacker

2179 Posts

Posted - 2014-06-16 : 01:39:16
Your welcome,

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

- Advertisement -