Error: 9002, Severity: 17, State: 6 The log file for database tempdb is full. Back up the transaction log for the database to free up some log space

  •  

    Hi

    I am looking at the current activity log in EM and I saw this message "The log file for database 'tempdb' is full. Back up the transaction log for the database to free up some log space .Under that activity it shows "Error: 9002, Severity: 17, State: 6.." What should I do to avoid this in the future?

    Thanks in advance

    LN

  • Hi LN,

    You need to tick the 'Automatically grow file' checkbox on the Transaction Log tab of the tempdb properties.

    Be aware that if you do this & you have massive queries filling tempdb, then it can grow quite large, but at least you won't get the log full message again !

    Hope that helps.

    Regards.

    j

  • Hi J,

    The automatically grow file was turned ON and is allowed to grow by 10% .why was it happening if it

    if it was turned ON

    Thanks,

    LN

     

     

     

  • Hi LN,

    Check to make sure that the disk the Log file is located on has available space for the log to grow.

    If you have the 'Automatic growth' option checked then the disk may be short of space, thus restricting Sql from growing the log file.

    Let me know if that helps.

    Regards.

    j

  • HI j,

    The disk has 101.G capacity and 29.35 G free space.This disk has only transaction logs stored.

    The data files are in a seperate drive

    Thanks ,

    LN

  • Hmmmm, world of the strange !!!

    Can you manually increase the tempdb Log file ??, if so perhaps this temporary measure will get you over your problem.

    Also forgot to ask you to check if you have 'Restrict File growth' option checked, simple I know but easily overlooked !

    Let me know.

    Regards.

    j

  • Hi J,

    The restricted file growth is not checked. The unrestricted file growth is checked.

    Why do I have to make the temp log files manually to a large size?

     

    Thanks,

    LN

  • Hi,

    Manually increasing the size was just a quick check to see if it would a) work & b) get you round your immediate issue.

    Have you tried to add another log file ?

    Also if you shutdown Sql & restart it, it should clear out the tempdb & the tempdb log file, which is another temporary solution, but alas still doesn't explain why it's not automatically growing

    Let me know.

    j

  • Check out this Microsoft article on different ways this error can occur.  Hope it will shed some light on your issue.

    http://support.microsoft.com/default.aspx?scid=kb;en-us;317375&Product=sql2k

     

  • That Microsoft page addresses standard databases, not tempdb. I'm having the same problem, and have been searching the web for resolution, but have yet to come across a clear explanation.

  • Restart the MSSQSERVER services which will reset the tempdb file sizes to its original size. then try enabling auto grow using MB instead of % basis. also keep performing transactinal log backup at regular interval. the log backup atregular intervals will keep the logical file of tempdb from growing.

    Regards,

    Sathya.

  • Hello,

    better let your file grow in fixed blocks of say 500mb instead of 10%

    Regards,

    Dewes

    Dewes van Lohuizen,
    DBA at CSC Netherlands
    Private interest: www.mikondo.nl

  • hi,

    a simple solution is to stop sql agent in management , and start it again.

    it will rename the database log file and create another file for itself.

    Best Regards,
    Ashkan

  • Hi, I am having the same problem...all settings appear to be correct & plenty of space on disk. Still looking.....

  • Kill or stop the long running transactions, if any.

    Change the recovery model to simple, if it is not.

    Release the unused space or shrink the log file.

Viewing 15 posts - 1 through 15 (of 16 total)

You must be logged in to reply to this topic. Login to reply