Log file growth

  • Admingod

    SSCertifiable

    Points: 5662

    Index maintenance and loading data conflict with each other cause the log file to grow and disk space ran out of space. Maintenance job failed cause log file full and impacted the load to fail. So would it be possible that index maintenance is rebuilding the same index where the load is happening? I would think in order not to happen this again either i am thinking run the loading process with smaller transactions and run the maintenance job in different time so that they don't conflict with each other. Would you think of anything else?

    Thanks in Advance!

  • John Mitchell-245523

    SSC Guru

    Points: 148434

    Make sure you run your index maintenance at a time when nothing else is likely to be going on.  Ensure you are taking frequent backups of your transaction log.  Consider switching to BULK LOGGED recovery mode during your load if the process performs any operations that could benefit from that.

    John

  • Admingod

    SSCertifiable

    Points: 5662

    Thanks John. During the bulk logged recovery mode since it's not fully logged operations, would that means i would be ability to lose point in time restore right?

  • Admingod

    SSCertifiable

    Points: 5662

    I’m not sure if we have a no activity window.  Users will be accessing and saving documents at any time to our system. Seems like huge loading process is a one time deal. However, there could be backfills of similar or greater volume, so this could end up occurring again. This is vendor application to load files into database. I’ve been loading one file in at a time. I am not seeing the log growth this time may be changing the frequency to run log backups more often than before. How can i check is it releasing the transaction after each of the file loads? Is that possible? Do you think it should be done during window of downtime?

  • John Mitchell-245523

    SSC Guru

    Points: 148434

    Yes, point-in-time recovery isn't possible from a log backup that includes a bulk-logged operation, but, crucially, you don't have to do a full backup when you switch back to FULL, as you would if you had switched to SIMPLE instead of BULK_LOGGED.  Confused?  This may explain it better.

    If you don't have any quiet times, then at least make sure your loads and index maintenance are scheduled at different times, and avoid the very busiest times.  You can use the log_reuse_wait_desc column in sys.databases to find out whether anything is preventing log space from being reused.  Check this after log backups, not after data loads.

    John

     

  • Admingod

    SSCertifiable

    Points: 5662

    My understanding is that while running Index Rebuild or Integrity checkdb would not make database unavailable and it will not affect users other than the performance issues. Any thoughts?

  • Jeff Moden

    SSC Guru

    Points: 995117

    Index rebuilds won't make a database go offline unless the logfile explodes large enough to fill the disk it's on.

    Index rebuilds will make the table data unavailable (which will obviously affect performance1) if you don't do them in an ONLINE fashion but there are some pretty cool workarounds for that (and you should look for workarounds because ONLINE rebuilds suck as almost as bad as REORGANIZE).

    IMHO, never use ONLINE or REORGANIZE unless someone holds a gun to your head or you've one something really stupid with the LOB datatypes.  If you've got VARCHAR(MAX) in your tables an you've not forced the large datatypes to go out of row, you've been a bit ignorant (I know I was) but REORGANIZE isn't going to be your savior even then.

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column.
    "If you think its expensive to hire a professional to do the job, wait until you hire an amateur."--Red Adair
    "Change is inevitable... change for the better is not."
    When you put the right degree of spin on it, the number 3|8 is also a glyph that describes the nature of a DBAs job. 😉

    Helpful Links:
    How to post code problems
    Create a Tally Function (fnTally)

Viewing 7 posts - 1 through 7 (of 7 total)

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