SQL Server Performance

tempdb logfile full error

Discussion in 'General DBA Questions' started by chetanjain04, Jun 19, 2006.

  1. chetanjain04 Member

    Hi,

    I am getting tempdb log file full errors.

    2006-06-19 02:56:18.21 spid61 Error: 9002, Severity: 17, State: 60
    2006-06-19 02:56:18.21 spid61 The log file for database 'tempdb' is full. Back up the transaction log for the database to free up some log space..0

    The recovery mode for tempdb is Simple. A quick google, helped to find that it is hitting a bug relating to auto checkpoints not occuring as expected. Following is the KB article referred:

    http://support.microsoft.com/kb/909369

    When I executed "backup log with no_log" things became normal. I want to know
    the best way to handle this without manual intervention.


    Thanks in Advance

    Best Regards,

    Chetan Jain
  2. balaganesh2k Member

    I think you can truncate the log information in frequent manner.
    backup log with truncate only. It will solve the log file problem.

    Ganesan B.
    Well defined problem is half way solved.
  3. chetanjain04 Member

    Thanks Balaganesh!

    Can I schedule the statement in a job.

    backup log tempdb with no_log.

    However, this would only be a workaround. Normally, we keep the size of the log file pre-allocated and it would rarely reqire to grow since the size is 32GB. The reason I say this is if the log requires more space then I can buy more storage. At this point, I do not know if the log requires more space or because auto checkpoints are not happening and hence the problem.

    Best Regards,

    Chetan
  4. ghemant Moderator

    Hi,
    you don't require to truncate tempdb log ,just restart the SQL Server service so that it will be in intial size ,in your application you are using tempdb / temptables very frequant !? alternatively you may set your tempdb size to more ,what kind of application you are running ?!

    Causes of SQL Transaction Log Filling Up
    http://support.microsoft.com/?id=110139
    http://www.support.microsoft.com/?id=317375
    Tempdb Log Exhausts the Available Disk Space When You Try to Update a Collection
    http://support.microsoft.com/kb/830029/en-us

    How to stop the transaction log of a SQL Server database from growing unexpectedly
    http://support.microsoft.com/?kbid=873235


    Hemantgiri S. Goswami
    ghemant@gmail.com
    "Humans don't have Caliber to PASS TIME , Time it self Pass or Fail Humans" - by Hemantgiri S. Goswami
  5. dtipton New Member

    Any evidence of blocking connections that may be "holding" open transactions? This could prevent the log from being truncated? DBCC OPENTRAN to find oldest open transaction.
  6. chetanjain04 Member


    We are having a datawarehouse application and the said error occurs in our Operational database in which we insert bulk data on a daily basis. The same process executes everyday but this is second time we have seen that the log file for tempdb has grown to this extent during the execution of this process. My first instinct was to compare the amount of data; but the row count does not differ much from the normal count.

    I have also checked that there were not many open transactions. In fact this process runs when there is no user activity in the database. I now believe that sometimes auto checkpoints are not occuring and due to which the inactive logs are not getting flushed. Hence, the increased size and log file full error!!. The same is explained inhttp://support.microsoft.com/kb/909369


    Best Regards

    Chetan

  7. suneelmehra New Member

    I would suggest move the tempdb log file to higher capcity drive. Calculate the approx size of your transactions and according to allocate the space to the drive. Initially tempdb database file installed on default directory like C:, which generally use for system files and allocate less space. Just move the log file to diff drive. Since tempdb in simple recovery mode, so log will truncate automatically.

    Thanks,
    Suneel
  8. chetanjain04 Member

    Truncation is what is not happening automatically sometimes (I do not know why but its given in the KB article). And it is only during such times I face the problem. I think I shall move tempdb to a higher capacity drive and schedule a job to truncate the log periodically/whenever the log file is full.

    Thanks to all for your help.

    Regards,

    Chetan
  9. satya Moderator

    By default TEMPDB recovery model is SIMPLE and you need not run a job to truncate the log for that database, rather check the transactions submitted or running queries has impact on this and try to regulate the queries in smaller batches to avoid sudden growth in Tlog.

    Satya SKJ
    Microsoft SQL Server MVP
    Contributing Editor & Forums Moderator
    http://www.SQL-Server-Performance.Com
    This posting is provided �AS IS� with no rights for the sake of knowledge sharing.
  10. chetanjain04 Member

    Hi Satya,

    Even I was under the impression that when it is SIMPLE then I need not truncate the log.

    However, I would like to explain that the tempdb transaction log file had grown upto 32 gb and my process was still running then but it was waiting for the log to grow further. I immediately did a "backup log tempdb with no_log" and it freed up 13 GB of space. I then looked for the problem on internet which lead me to find abouthttp://support.microsoft.com/kb/909369. This article describes that Automatic checkpoints on some SQL Server 2000 databases do not run as expected. Hence, I was looking at the best possible wayout other than the suggested hotfix since I do not have a test environment for this. Also, there is very little we can do about the queries because they are very simple queries like "insert into select * from .....".


    Regards,

    Chetan
  11. satya Moderator

    True and only in some extent it happens on the TEMPDB and it seems your system falls in the same category. If you are happy with this process then continue as usual.

    Satya SKJ
    Microsoft SQL Server MVP
    Contributing Editor & Forums Moderator
    http://www.SQL-Server-Performance.Com
    This posting is provided �AS IS� with no rights for the sake of knowledge sharing.

Share This Page