Post reply

Enormous logfile growth

  • July 2, 2004 at 8:22 am

    #109075

    Hi there,

    There is a monthly job running to cleanup a table. Basically it says:

    DELETE Tablename WHERE tblDate < @tblDate

    Let's say the variable @tblDate = 2004/6/1

    The table is 60.000.000+ large; recoverymodel is simple; the columk tblDate is indexed. The log grows to over 70 Gb... and it then hits the diskspaceboundery.

    Why is it, that this deleteaction has such a loggrowth? Is there a way to prevent this? I am considering to let the proces run weekly, but I would rather not, for several reasons. Is there an alternative? We really need to clean that table up, it grows about 5 Gb a month.

    Any ideas?

    Greetz,
    Hans Brouwer

  • July 2, 2004 at 10:17 am

    #513201

    Try dropping all the indexes apart from the index in the query. This should then reduce the transaction log size considerably. Then put the index back on and dbcc reindex. It may not save you a lot of time, but I think it will be quicker.

    Alternatively, delete the rows in smaller batches and truncate the log as you go along.

    ------------------------------
    The Users are always right - when I'm not wrong!

  • July 2, 2004 at 11:22 am

    #513206

    Another option would be to break your delete into smaller batches with a "CHECKPOINT" between them. The checkpoint will initiate a log truncation and should help keep your log smaller.

    Steve

Viewing 3 posts - 1 through 2 (of 2 total)

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