Post reply

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 8:00 am

    #108298

    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.." What should I do to avoid this in the future?

     

    Thanks in advance,

    LN

  • May 5, 2004 at 10:40 am

    #505340

    Hve moved this to sqlserver administration forum

  • May 6, 2004 at 8:56 am

    #505491

    First I would check the properties for tempdb for this server.  If restricted growth is selected and the size is relatively small and you have a monster sqr or query running it could fill it up in a hurry.  Also where is tempdb physically located.  If it is on the C drive and you have unrestricted selected it could grow until it fills up the C drive.  If it is located on the C drive I would recommend moving it off to the D drive or other.  Tempdb is usually very small but I have seen it grow to 10GB on a big database with a monster sqr with zero commits.  You can also restart SQLServer and it will release the resources that tempdb is holding.

    cortec

  • May 6, 2004 at 5:40 pm

    #505575

    I've seen this error when running a query/process/sp that uses a lot of temp tables specified with a # at the beginning.  A way to get around this is to create actual working tables that stick around or use table variables instead of temp tables with #.

  • June 30, 2004 at 9:02 pm

    #512788

    I have had this message a couple of times since applying SP3 to SQL2K.

    We have massive free disk space (40GB+) and only a small logfile on tempdb (200Mb).

    I have seen a number of other people post similar messages on the web and the consensus seems to be that this is actually a spurious message in that the queries causing the message run to completion without error. The message may be avoided by increasing the file growth percentage increment.

    I have not verified that this is the case but have seen no evidence to the contrary.

    I think this buggy behaviour may have been introduced in SP3.

     

    See http://www.devdex.com/sql/message.asp?p=&r=4282263&page=2

     

  • July 24, 2007 at 1:56 pm

    #722042

    1. Go to Enterprise Manager > Databases > TEMPDB - Right Mouse Click and select Properties - Options - Model must be set to Simple.

    2. Enterprise Manager > Databases > All Tasks - Shrink Databases.

    3. Upgrade to SQL SP 4 latest hotfix, which has a fix for similar issue.

Viewing 6 posts - 1 through 5 (of 5 total)

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