August 14, 2017 at 9:41 am
Setting up a new server and going with Brent's recommendation to just create 8 tempdb files and pre-size them to fill the drive.
As stated
Size them (and the log file) to fill up the drive
But what size for the log file? Is there a good percentage to use? I see a data to log ratio of about 6:1 for one of our tempdbs and another is about 16:1
Making them all the same would be 8:1, just looking for a suggestion as a starting point.
This is a data warehouse server running SSIS so it will be moving large amounts of data. The 6:1 is another SSIS server.
August 14, 2017 at 10:26 am
Some experts say that the log file for tempdb should be twice the size of a single data file and to set the log file auto-growth to a hard value such as 512 MB.
August 14, 2017 at 10:31 am
OK, but that could be variable percentage based on the number of files used unless you meant that based on having 8 files.
Also Auto-growth will be turned off since the files will be sized to fill the available space. The data is on our SAN so we only get a carved out chunk of space. If we need more it gets expanded and the files will be resized to max out the new space.
August 14, 2017 at 4:41 pm
My personal preference is to not fill the disk. Start the database out at a reasonable size.
Filling the disk means you may get a surprise one day when it is full and your IT department says you need to wait 3 weeks to get more disk.
It may be different where you work, but getting more disk is painful where I work. So I try to keep the disk a decent size, set autogrow to a reasonable value and watch the growth.
Windows will have issues if the disk is 100% full. If your IT team has automatic defrag or chkdsk running on it, having 0% free will make that process horrendously slow and may even cause it to fail. And I've seen some nasty things happen back in the windows 98 days with defrag crashing in the middle and causing the disk to corrupt. I imagine it is a lot more stable and crash-friendly now, but I've also not tried force-killing a defrag in the middle.
Now, Brent is a super smart guy and I'm not meaning to contradict him, but I know I get grumpy and the IT department gets grumpy when we see any disk with less than 20% free. And one user in the comments on the link you provided indicated that when his tempdb got full one time, it crashed SQL. His words were "SQL will crash (reboot). Yep. Had it happen and its not a fun event.". What I don't know from reading taht is did it restart the SQL instance OR did it reboot the whole server/VM?
I'd much rather have it auto-grow (with instant file initialization turned on to keep the grow fast) and get an alert when the disk has less than 20% free than think things are running happily and get users calling me because my server rebooted during critical uptime.
If you read the comments in the link you provided, and you wish to go with filling the disk, they are recommending you take the largest object in the database, add 50% to it and make that your log size. And hope that you never have a larger object in there.
The above is all just my opinion on what you should do.
As with all advice you find on a random internet forum - you shouldn't blindly follow it. Always test on a test server to see if there is negative side effects before making changes to live!
I recommend you NEVER run "random code" you found online on any system you care about UNLESS you understand and can verify the code OR you don't care if the code trashes your system.
August 16, 2017 at 6:20 am
Tom Van Harpen - Monday, August 14, 2017 9:41 AMSetting up a new server and going with Brent's recommendation to just create 8 tempdb files and pre-size them to fill the drive.
As statedBut what size for the log file? Is there a good percentage to use? I see a data to log ratio of about 6:1 for one of our tempdbs and another is about 16:1Size them (and the log file) to fill up the drive
Making them all the same would be 8:1, just looking for a suggestion as a starting point.
This is a data warehouse server running SSIS so it will be moving large amounts of data. The 6:1 is another SSIS server.
I would prefer 4:1 would be ideal option and i wont recommend to fill up the drive as it brings SQL server to halt, so all file total max should be 80 to 85% of total disk.
Regards
Durai Nagarajan
August 16, 2017 at 6:26 am
bmg002 - Monday, August 14, 2017 4:41 PMI'd much rather have it auto-grow (with instant file initialization turned on to keep the grow fast) and get an alert when the disk has less than 20% free
Same.
August 16, 2017 at 8:58 am
Beatrix Kiddo - Wednesday, August 16, 2017 6:26 AMbmg002 - Monday, August 14, 2017 4:41 PMI'd much rather have it auto-grow (with instant file initialization turned on to keep the grow fast) and get an alert when the disk has less than 20% freeSame.
But at times the TEMPDB grows faster and fills up the max allowed in no time. on this scenario it is risker to have unlimited growth.
Regards
Durai Nagarajan
August 16, 2017 at 9:21 am
durai nagarajan - Wednesday, August 16, 2017 8:58 AMBeatrix Kiddo - Wednesday, August 16, 2017 6:26 AMbmg002 - Monday, August 14, 2017 4:41 PMI'd much rather have it auto-grow (with instant file initialization turned on to keep the grow fast) and get an alert when the disk has less than 20% freeSame.
But at times the TEMPDB grows faster and fills up the max allowed in no time. on this scenario it is risker to have unlimited growth.
I have personally not noticed this. One of the systems I manage has a 512 MB tempdb database with a 32MB log. We have several hundred GB free on that drive (it is shared with other things... I know, that is bad, but it is what it is). We just don't run many queries that dump a lot of data into tempdb on that system. Most queries are just pulling data from a single table with that system.
I know windows will get grumpy if you try to do any maintenance on a full disk. And SQL will do unpredictable things if it runs out of space in tempdb.
I was not recommending unlimited growth; I would set it to a fixed limit still (slightly smaller than the size of the disk) and then set up some flavor of alerting when the disk has less than 20% free. Start tempDB out at a reasonable size, set the growth to a fixed value (ie not a percent) and then monitor it.
I would never set a database to unlimited growth... that is just asking for trouble. It is just as bad as not setting a max memory value.
The above is all just my opinion on what you should do.
As with all advice you find on a random internet forum - you shouldn't blindly follow it. Always test on a test server to see if there is negative side effects before making changes to live!
I recommend you NEVER run "random code" you found online on any system you care about UNLESS you understand and can verify the code OR you don't care if the code trashes your system.
August 16, 2017 at 9:49 am
Thanks everyone for the replies I see there is a common theme here and i tend to agree that i'd rather not be surprised if something did fill up.
Instant init is on and also looking at trace flag 1117 as a startup parameter to grow all files in a file group equally. Of course this applies to every database.
For SQL 2016 use AUTOGROW_ALL_FILES option of ALTER DATABASE and do this for tempdb
So I think a little bit of everything is the key.
8 Tempdb data files sized to fill the drive but leaving a margin for monitoring. This way the files should not have to autogrow but can if it gets to that point.
Autogrow at a good size chunk (512 like the first poster mentioned) which would amount to 4 GB total (with trace flag 1117 on or AUTOGROW_ALL_FILES)
Auto file init turned ON
and a log file initially sized at 20% of total with the same auto grow amount.
What I was looking to accomplish is having a disk config that we can document for our server setups and I think this makes sense.
Edit:
Also include max size of the files set at just under the total space available.
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply