Tempdb Log file best practices

• Robin35

  SSCertifiable

  Points: 6471

  More actions

  January 6, 2017 at 12:38 pm

  #401323

  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.

• rvsc48

  SSCertifiable

  Points: 7756

  More actions

  January 6, 2017 at 1:11 pm

  #1921192

  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.

• Robin35

  SSCertifiable

  Points: 6471

  More actions

  January 6, 2017 at 1:52 pm

  #1921204

  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.