August 2, 2011 at 12:49 pm
Currently tempdb is set to autogrow by 10 percent. and maximum file size is restricted to 60GB
This is due to huge amounts of replication, etc, which we are aware needs lots of tempdb, but the ceiling we previously had, has now seemed to be breached. Think its about time to increase the max size. But had a few questions.
1) Is autogrow by 10 % right? Because its much greater than 500 MB?
Or should it be autogrow by MB? if MB, on what basis do I calculate?
2) Does this change of max size, (autogrow reconfig if required) require a services restart? or does it take effect automatically?
August 2, 2011 at 12:51 pm
You should set it at some MB (or GB).
Try to keep it so that it can extend in 1-2 sec max.
On another note, I'd change the initial size so that the server doesn't to re-grow that file after every reboot.
August 2, 2011 at 1:23 pm
Ninja's_RGR'us (8/2/2011)
You should set it at some MB (or GB).Try to keep it so that it can extend in 1-2 sec max.
On another note, I'd change the initial size so that the server doesn't to re-grow that file after every reboot.
"Try to keep it so that it can extend in 1-2 sec max." - I was thinking of 2 GB for the log file, just was not sure if that is good enough for 1-2 seconds. Please let me know.
"I'd change the initial size so that the server doesn't to re-grow that file after every reboot." - There are about 16 data files of 4 GB each initial size. Only half of them are set to autogrow by 10%.
Also, do I need to restart sql services for the change to take effect?
August 2, 2011 at 1:28 pm
No need to restart.
I don't know how fast your disks are so you need to test to find a good size.
Also you have to keep that in mind >>> http://www.sqlskills.com/BLOGS/KIMBERLY/post/Transaction-Log-VLFs-too-many-or-too-few.aspx
August 2, 2011 at 1:35 pm
Thank You. I will test the auto growth setting and see what is best..
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply