Post reply

Tempdb Full !!!!!!!!!

  • January 3, 2006 at 8:24 am

    #111130

    Received errors: 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 !!???

    Can or should the TempDB be backed up?

     

    My TempDB shows Size 34mb with space available 29mb

    unrestricted file growth on data and log file with 'grow by 10%' set.

    not sure what to do about this one..   can anyone help!!!!!!

     

  • January 3, 2006 at 8:49 am

    #612415

    Have you checked available disk space on the drive that tempdb is stored on?

    Seems like there is no space.

    tempdb does not need to be backed up.

     

  • January 3, 2006 at 8:53 am

    #612417

    Probably you have a run-away query.  If you can figure out which query it is, make sure it is no longer running.  (It shouldn't be.) You may need to shrink the database to free up hard disk space (you may be out of it.)

    Also your tempDB should be in simple recovery mode, not full.  Change it and shrink the log file.  I think this may be more likely.

    All else fails, re-start SQL Server, that will re-build tempDB

  • January 3, 2006 at 9:03 am

    #612419

    THanks!   My TEMPDB DB is already in Simple recovery Mode.

    The data file for TempDB is 23mb and there is over 50GB free on that drive.

    The Log file for TempDB is 12 mb and there is over 80GB free on that drive.

    Not sure why this is happening if the data and log files are set to autogrow and there is plenty of Disk space for growth.

    I know that the TempDB gets rebuilt when SQL Server is restarted - we rarely reboot this Server but it we actually rebooted last week in order to pull in some OS changes..

    How can I tell if there is a long running query?

     

  • January 3, 2006 at 10:59 am

    #612449

    Your problem might be that your TEMPDB can't grow fast enough for a transaction.

    I never set mine for percentage, I always do MBs.

    Try setting yours for 50 MB for each.

    -SQLBill

  • January 3, 2006 at 11:04 am

    #612450

    Thank you!!!  That makes alot of sense.

    I'll change the 'grow by' from 10% to a static number.. 

    like 50 MB.   I'm assuming I can do this at anytime..  don't have to wait until after hours?

  • January 3, 2006 at 12:31 pm

    #612466

    Nope, just do it right now.

    -SQLBill

  • January 3, 2006 at 12:39 pm

    #612469

    tanks!!

Viewing 8 posts - 1 through 7 (of 7 total)

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