Post reply

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

  • May 5, 2004 at 10:37 am

    #85322

     

    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

  • May 6, 2004 at 1:57 am

    #505418

    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

  • May 6, 2004 at 6:54 am

    #505458

    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

     

     

     

  • May 6, 2004 at 7:13 am

    #505463

    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

  • May 6, 2004 at 8:18 am

    #505486

    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

  • May 6, 2004 at 8:27 am

    #505487

    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

  • May 6, 2004 at 8:49 am

    #505490

    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

  • May 6, 2004 at 9:19 am

    #505500

    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

  • May 6, 2004 at 10:20 am

    #505518

    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

     

  • October 5, 2007 at 5:39 pm

    #739759

    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.

  • October 6, 2007 at 9:08 am

    #739790

    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.

  • July 1, 2010 at 8:30 am

    #1187687

    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

  • August 16, 2010 at 11:16 pm

    #1207656

    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

  • November 29, 2010 at 6:24 pm

    #1256559

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

  • November 30, 2010 at 2:38 am

    #1256656

    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