September 23, 2008 at 9:22 am
How should I know how much space I need for dev and log in the tempdb
September 24, 2008 at 7:51 am
Good question. But what ever space you provide to begin with at the end of the day you have to give tempdb the space it needs. I would begin with some random value on a drive and then leave it as auto growth enabled. Just monitor how it goes, if you guys are not doing too many data manipulations then you should be fine with the what ever limit you specified.
If a one off process takes all the space on the drive for tempdb to grow then you can always shrink the log right?
Hope this helps...
The_SQL_DBA
MCTS
"Quality is never an accident; it is always the result of high intention, sincere effort, intelligent direction and skillful execution; it represents the wise choice of many alternatives."
September 24, 2008 at 8:48 am
Thank you. This what I have and don't know if this is correct:
My temp tempdev: is 8MB and templog: is 1MB. My largest db is 630MB. Can I change from 8MB to 500mb and from 1 to 258 (20%) than biggest db, is this correct?
Here is my setting for temp DB:
DBSize Unalocated size
tempdb 9.00 MB 7.22 MB reserve data index_size unused
800 KB 248 KB 384 KB 168 KB
tempdev
PRIMARY 8192 KB Unlimited 10% data only
templog
NULL 1024 KB Unlimited 10% log only
Also, do you usually do use unrestricted file grows or restricted, if restricted how do you run by? Can I changed settingis anytime of tempdb or I have to restore SQL Server?
September 25, 2008 at 3:25 am
tempdb size does not depend on the size of your largest DB.
My advice is to follow the advice of The_SQL_DBA.
When you know how much space is actually used, then make a permanent change to the size of your tempdb file. You do not want to have tempdb growing while SQL Server is in use because every time tempdb grows the query that needs the extra space is halted until the file growth is complete. You also get disk fragmentation, which is not good for SQL Server performance.
However, until you know how much space you need you have to live with the performance hits. But you need to take action as soon as possible to avoid continually getting the problem.
Original author: https://github.com/SQL-FineBuild/Common/wiki/ 1-click install and best practice configuration of SQL Server 2019, 2017 2016, 2014, 2012, 2008 R2, 2008 and 2005.
When I give food to the poor they call me a saint. When I ask why they are poor they call me a communist - Archbishop Hélder Câmara
September 30, 2008 at 7:57 am
A side question. I know that when you reboot, tempdb is rebuilt based upon the model database (privileges, etc.). Is the initial size of tempdb based upon the size of model?
Steve
September 30, 2008 at 8:07 am
sblock (9/30/2008)
A side question. I know that when you reboot, tempdb is rebuilt based upon the model database (privileges, etc.). Is the initial size of tempdb based upon the size of model?Steve
No, the initial size of tempdb is not based on model.
😎
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply