Results 1 to 2 of 2
  1. #1
    Join Date
    Aug 2003
    Posts
    12

    Unanswered: Transaction log full

    Hi All,

    I am trying to run a UPDATE on a large table with 4 self-joins. But everytime I keep getting the message that the transaction log for the database is full.
    Even though I try to get rid of the log file with the foll statement "import from g:\message.txt of del replace into sample1" (which I got from dbforums) I still keep getting the error message and unable to complete the UPDATE statement.
    Can someone please help me with this...???

    Shyam.

  2. #2
    Join Date
    Aug 2001
    Location
    UK
    Posts
    4,650

    Re: Transaction log full

    If you are trying to update a large table , details about every change is written to the transaction log ... By default, your transaction can span over a maximum of logprimary+logsecondary (these are db cfg parms) log files ... If you are on Version 8, setting logsecond to -1 and enabling logretain and/or userexit will allow infinite active logging ... In other words, you will not get this error ...

    In your case, my suggestion will be to commit periodically, for which you can use a stored proc similar to one posted here

    http://dbforums.com/showthread.php?t...17#post1715617

    Or, if using the UPDATE Statement is the only option you have, increase the logprimary, logsecondary, logfilesz etc to suit your needs ...

    BTW, IMPORTing from a blank file will clear the table without generating logs, which is the reason, that is a recommended way of deleting all records from a table ...


    Cheers,

    Sathyaram

    Originally posted by shyams75
    Hi All,

    I am trying to run a UPDATE on a large table with 4 self-joins. But everytime I keep getting the message that the transaction log for the database is full.
    Even though I try to get rid of the log file with the foll statement "import from g:\message.txt of del replace into sample1" (which I got from dbforums) I still keep getting the error message and unable to complete the UPDATE statement.
    Can someone please help me with this...???

    Shyam.
    Visit the new-look IDUG Website , register to gain access to the excellent content.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •