Results 1 to 6 of 6
  1. #1
    Join Date
    Feb 2007
    Location
    The Farm
    Posts
    35

    Unanswered: Maxing out log files when using SIMPLE

    We have our databases set to SIMPLE model, but we continue to blow up during DataWarehouse loads because the log file fills. Our understanding is that SIMPLE should have no logging yet it appers to. I have 2 requests.

    1) Is there a command to tell the database prior to the DataWarehouse load to not log any transactions?

    2) Do we need to do more then set the DB to SIMPLE to get a general "no log" effect?

    Thanks,
    Todd
    Bartron

    Liv'n down on the cube farm. Left, then another left, then right.

  2. #2
    Join Date
    Jan 2003
    Location
    Massachusetts
    Posts
    5,800
    Provided Answers: 11
    1) No. Logging is never entirely turned off. The transaction log is simply not saved when you are in SIMPLE recovery mode.

    2) Probably. The culprit is ikely to be your code. I would guess there is either:
    a) A begin transaction statement somewhere way at the top of the process
    or
    b) the BCP/DTS load command is not batching the rows it is inserting.

  3. #3
    Join Date
    Feb 2007
    Location
    The Farm
    Posts
    35
    I know the culprit is the code. There is a join of massive tables. We had gone the batching route but it still was blowing up. We have ben increasing the max size of the log but were hoping we could stop going that direction.

    Thanks
    Bartron

    Liv'n down on the cube farm. Left, then another left, then right.

  4. #4
    Join Date
    Jan 2003
    Location
    Massachusetts
    Posts
    5,800
    Provided Answers: 11
    If this is a complete refresh of the table (meaning you can drop it before running the insert), you can try to write the insert as SELECT INTO.

  5. #5
    Join Date
    Nov 2005
    Posts
    122
    With SIMPLE recovery, the log is not needed for backup, but SQL Server still needs it for recovery. Therefore every transaction is still logged, causing the log file to expand if there is no free space.

    A section of a log file is marked for reuse as soon as all transactions are committed or rolled back and they are not needed for recovery, meaning the data has been written to disk during a checkpoint.

  6. #6
    Join Date
    Feb 2007
    Location
    The Farm
    Posts
    35
    Thanks for all the input
    Bartron

    Liv'n down on the cube farm. Left, then another left, then right.

Posting Permissions

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