Is there any tempDB initial size recommendation for best practice instead of sticking to the default SQL server configuration? Probably use it for future SQL installs.
Also, reason I'm asking is that one of our server has a 30GB disk for tempdb. We got disk full error yesterday on the drive (separate story). So I restarted the SQL service and when I checked, tempDB it was now 19GB. Confused on why it was so large then found out on the configuration that the configured size was set at 19GB Autogrowth 10%, Unlimited.
TIA
If you search for "sql server temp db best practices", one of the top results is this link from Brent Ozar:
It is probably very common practice now to have at least 4 data files for tempdb, all with the same size to fit in the drive space you have, and then turn off auto-growth completely. In other words, start the data files at the maximum size they should be and don't allow growth of the files. If performance becomes an issue, increase the drive size and add more files--making sure to keep them all the same size and turning off auto-growth.
So for your scenario, you would need to shrink the 1 existing data file, set the size to be 7 GB, add three more data files at 7 GB making sure to set auto-growth to off. Then your 30 GB drive will be filled to around 28 GB but you will not get an out of drive space issue any more. As a side note...be sure to tell your sysadmins that you're doing this because a lot of times they use monitoring software to see when drives are getting full, and it will show them that the drive is always full.
Alan H
MCSE - Data Management and Analytics
Senior SQL Server DBA
Best way to ask a question: http://www.sqlservercentral.com/articles/Best+Practices/61537/
January 29, 2020 at 6:51 pm
Just to add...if you do use autogrowth, don't use a percentage. It can end up biting you. Use a fixed growth size that's reasonable for your storage system.
Sue
January 30, 2020 at 5:19 pm
So for your scenario, you would need to shrink the 1 existing data file, set the size to be 7 GB, add three more data files at 7 GB making sure to set auto-growth to off. Then your 30 GB drive will be filled to around 28 GB but you will not get an out of drive space issue any more.
What would a reasonable log file initial size be set to? On the link, the tempdb log file was created with the same disk space allotment as the data files.
So if I have 30GB disk space, I would create 4 datafiles and 1 log file of 6GB each. Wouldn't a 6GB log file an overkill because when I checked any of our SQL servers, the tempdb log file is way smaller than the data files (never bigger than 5% data to log).
TIA
January 30, 2020 at 5:59 pm
I forgot about the log file in my example...probably because I usually don't put the log file with the data files. Yes, if you're going to have the log file with the data files, you have to account for its size and growth.
To be honest, I've never had to be concerned with the log file for tempdb because it has always been on a different drive that has plenty of space.
Perhaps others have some experience here and can give you a good suggestion about how to size and set auto-growth for the log file.
Alan H
MCSE - Data Management and Analytics
Senior SQL Server DBA
Best way to ask a question: http://www.sqlservercentral.com/articles/Best+Practices/61537/
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply