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 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply