locked
troubleshooting full tempdb RRS feed

  • Question

  • Today our applications started logging the following error for a brief period of time (maybe 1 minute total).

    Message: The transaction log for database 'tempdb' is full.

    Checked the drive that tempdb is on and it had plenty of space and by that time the error had stopped.   How do you troubleshooting this type of error postmortem to figure out what caused the issue in the first place?

    sql 2008


    Tuesday, February 26, 2013 9:22 PM

Answers

All replies

  • First thing to check , since you already mentioned drive has plenty of space , Is the TempDB  log file set to auto growth ??

    Hope it Helps!!

    Tuesday, February 26, 2013 9:37 PM
  • First thing to check , since you already mentioned drive has plenty of space , Is the TempDB  log file set to auto growth ??

    Hope it Helps!!

    Autogrowth is enabled.
    Tuesday, February 26, 2013 10:04 PM
  • First thing to check , since you already mentioned drive has plenty of space , Is the TempDB  log file set to auto growth ??


    Hope it Helps!!

    Autogrowth is enabled.
    and no one has re started sql server service when this happened??whats the creation date on your TempDB??

    Hope it Helps!!


    • Edited by Stan210 Tuesday, February 26, 2013 10:22 PM
    Tuesday, February 26, 2013 10:22 PM
  • First thing to check , since you already mentioned drive has plenty of space , Is the TempDB  log file set to auto growth ??


    Hope it Helps!!

    Autogrowth is enabled.

    and no one has re started sql server service when this happened??whats the creation date on your TempDB??

    Hope it Helps!!


    select name, crdate from sys.sysdatabases WHERE name = 'tempdb'

    tempdb, 2012-11-19 22:11:27.750

    Tuesday, February 26, 2013 10:30 PM
  • does the sql errro logs say anything???

    Hope it Helps!!

    Tuesday, February 26, 2013 10:41 PM
    • Marked as answer by scott_m Wednesday, February 27, 2013 5:24 AM
    Tuesday, February 26, 2013 11:29 PM
  • You might also want to check what the maximum file size setting is for your tempdb data files.
    Tuesday, February 26, 2013 11:43 PM
  • Hi Scott

    Please check below
    1. Wats max size setting for tempdb file growth
    2. How file growth is happening In percent or In MB. If its MB it should not be like 1 MB or 10 MB. It should be at-least 5 % of your file size.

    Thanks

    Saurabh Sinha

    http://saurabhsinhainblogs.blogspot.in/

    Please click the Mark as answer button and vote as helpful if this reply solves your problem


    Wednesday, February 27, 2013 5:31 AM