1

I have a SQL Server 2008 R2 that started showing that the TempDB transaction log was full. I checked the log file settings and there was no growth restriction and there was plenty of disk space.

I had no luck on finding an explanation of what could have caused this.

Has anyone experienced this in the past?

Thank you.

Improve this question

1 Answer 1

Reset to default
2

I've seen this occur when the file growth setting is set too high, so the growth event takes too long. Obviously this depends on your storage environment and performance details.

In my case, the 10% default growth rate was fine when the database was small, but at some point when the database got huge enough that the growth event took over 30 seconds, which was long enough that the original query timed out and rolled back. The rollback then cancelled the growth event, until the application re-tried the same query, and we got an endless loop.

So check your growth rates, or just pre-size your tempdb to a larger size.

Improve this answer
9
  • And never use percentage growth
    – Nic
    Commented Dec 1, 2016 at 20:29
  • @Nic That too, although that requires micro-managing the growth rates as the database gets larger. You don't want a 1GB growth rate on a 10MB database, after all. I try to leave it as a % for very tiny databases, and a fixed amount for medium-to-large. In the case of this question, of course, TempDB should definitely be a fixed rate.
    – BradC
    Commented Dec 1, 2016 at 20:31
  • Thanks for the answer. The tempdb transaction log is set to 250 MB, Unlimited. Maybe it is too small
    – Jose Chama
    Commented Dec 1, 2016 at 20:35
  • Growing to 250 mb should not (typically) time out.
    – paparazzo
    Commented Dec 1, 2016 at 20:38
  • @JoseChama I obviously don't know anything about the other databases and activity on your server to know whether a 250MB tempdb log is big enough, but yeah, try manually growing it to a larger size. But that doesn't answer the other part of my question, how is it set to grow? By a fixed amount? Or by a percent?
    – BradC
    Commented Dec 1, 2016 at 20:40

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.