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 |
|
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 RegardsSyed |
|
|
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 |
 |
|
|
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 ? |
|
|
|
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 |
|
|
|
jackv
Master Smack Fu Yak Hacker
2179 Posts |
|
|
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. |
|
|
|
jackv
Master Smack Fu Yak Hacker
2179 Posts |
Posted - 2014-06-16 : 01:39:16
|
| Your welcome,Jack Vamvas--------------------http://www.sqlserver-dba.com |
|
|
|
|
|
|
|
|
tempdb size