January 6, 2017 at 12:38 pm
Hello,
We have an issue with one of our application, when they run a process it fills up the tempdb log file. Tempdb log file size is pre allocated with 61Gb and total disk size is 67Gb.
i know we have to look at the stored procedure to figure out what filling up the tempdb log file but since the stored proc is encrypted i dont have any visibility to the code.
my question is, is it a good practise to resixe tempdb log file with 1Gb and allow auto growth with 1024MB increment ? if not what is the good practice for tempdb log ?
thanks in advance.
January 6, 2017 at 1:11 pm
Hi Robin, it definitely sounds like there is much more space needed for the tempdb log file. Pre-sizing the log file is much better than allowing it to auto-grow. One of the most recent recommendations has been to pre-size the log file for tempdb to two times the size of one of your tempdb data files. For example, if you have 9 (10gb) data files for your tempdb, you would want to double the size of one of them (10gb * 2) = 20gb and pre-size the tempdb log file to this amount. As far as the AutoGrowth, as mentioned, you want to avoid auto-growth as much as possible, but, allowing it to auto-grow by a set size is acceptable, such as 512mb. A thing to keep in mind is while the file is auto-growing, performance always suffers as there will be waits while the file is being expanded.
January 6, 2017 at 1:52 pm
Thanks a lot for your quick response.
Yes, we have four tempdb data file with 30GB each and our tempdb log file is pre-allocate with 60GB. I'm in the process of reviewing the stored procs now. Thanks.
Viewing 3 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply
This website stores cookies on your computer.
These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.
To find out more about the cookies we use, see our Privacy Policy